Q: Pivot/SUMIF Help

Question

FlaParrotHead

Level 1 (5 points)

Wireless

Q: Pivot/SUMIF Help

OK -- Rather then asking too many individual questions let me give an example for the Numbers experts (I am coming from many years of Excel)

Month	Date	Description	Original Description	Amount	Transaction Type	Category	Account Name
April	April 13, 2016	Delta	DELTA AIR	$725.14	debit	Air Travel	Visa1
April	April 11, 2016	Parking	PARKING	$46.97	debit	Air Travel	Visa2
April	April 11, 2016	Southwest Airlines	SOUTHWESTAIR	$5.00	debit	Air Travel	Visa1
April	April 19, 2016	DR D	DR D	$160.00	debit	Doctor	CREDIT CARD
April	April 19, 2016	Electric Company	Electric Company Bill Payment	$378.89	debit	Electric	CHECKING
April	April 11, 2016	Amazon	AMAZON MKTPLACE	$15.66	debit	Electronics	Discover
April	April 1, 2016	New Seasons	NEW SEASONS MARKET	$125.47	debit	Food & Dining	visa1
April	April 21, 2016	Publix	PUBLIX	$145.23	debit	Groceries	Visa2
April	April 13, 2016	Wal-Mart	Wal-Mart	$47.77	debit	Groceries	CHECKING
April	April 11, 2016	Apple	APPLE	$497.65	debit	Hobbies	Visa1
April	April 15, 2016	Vacations	VACATIONS	$775.47	debit	Hotel	Visa1
April	April 12, 2016	Mark	PAYPAL	$100.00	debit	Hotel	CHECKING
April	April 8, 2016	Verizon	VERIZON	$115.55	debit	Phone, Internet, TV	CHECKING
April	April 19, 2016	Marchellos	MARCHELLOS	$38.87	debit	Pizza Delivery	CREDIT CARD
April	April 8, 2016	Tri-Met	TRIMET	$2.50	debit	Rental Car & Taxi	Visa2
April	April 4, 2016	Rainbow Market	RAINBOW MARKET	$21.25	debit	Travel Groceries	Visa2
April	April 1, 2016	R O C	R.O.C.C. Bill	$67.40	debit	Water	JOINT CHECKING
April	April 2, 2016	Amazon	AMZ	$138.44	debit	Woot Wine	Discover

Imagine the above sheet with many more lines, up to 12 months (obviously) and many more Categories. I am looking to build reports, by month, by category, with subtotals for Amount at the Category and the Month Level. Hopefully a report that I'll easily be able to also create charts/graphs from.

In the Excel days, this is all very easy using Pivot tables --- not such the case with Numbers but I'd like to learn from example and use Numbers if possible.

iMac, OS X El Capitan (10.11.2)

Posted on Apr 22, 2016 11:59 AM

I have this question too

SGIII

Level 6 (10,622 points)

Mac OS X

A:

Not as easy as Pivot Table drag-and-drop but not really that hard. One formula, in B2, that you can fill right and down. Distinct category names are listed down the left. Month names across the top. I changed some of the months in your sample dataset to show

The names have to match exactly. Note that when you type in month names Numbers automatically converts them to a date-time value and the existence of the time part of that or the year part of the date can cause puzzling failures to match. So it can be safer to type a ' followed by the month name. That tells Numbers to treat the name as text.

The formula in B2, filled right and down:

=SUMIFS(Table 1::$E,Table 1::$G,$A2,Table 1::$A,B$1)

You can read more about SUMIFS and see examples here. It works with column-condition pairs.

SG

Posted on Apr 22, 2016 3:22 PM

Q: Pivot/SUMIF Help

All replies

Helpful answers

Page 2

by Barry,

Barry Jul 11, 2016 5:40 PM in response to flashkube2
Level 7 (32,271 points)

Jul 11, 2016 5:40 PM in response to flashkube2

Hi Flashkube,

The discussion in my post earlier today applies to these two tables, posted near the beginning of this thread by SGIII.
The formula, shown immediately above this line, is in the selected cell, B2 of Table 2.
The cells (and ranges of cells) included in the formula are highlighted in the image with the same colours as the tokens in the formula as shown.
With the table where you can see it, you might have a better opportunity to see what is going on.

The sum in the selected cell (777.11) includes only the first three amounts in Column E, because they are the only ones on the table that satisfy both conditions of the SUMIFS formula.

--- --- -- --- --- --

If you're still stuck after doing that, I'd suggest starting a new thread with your question to eliminate the need to scroll through/bypass all the existing material in this thread, and to avoid the focus on only one possible solution.

Give a description of what you want to accomplish, without reference to the method (formula) you think is best to do the job. Add details on the input data and its form (how it's placed on the table) and the end goal. A screen shot of a sample table using real or made up data will likely be useful.

There are usually several ways of accomplishing the same task, and keeping the focus on the starting point and the goal opens the door to solutions from several sources.

Regards,
Barry
Reply options

Link to this post

by flashkube2,

flashkube2 Jul 12, 2016 8:40 AM in response to Barry
Level 1 (11 points)

Mac OS X

Jul 12, 2016 8:40 AM in response to Barry

I tried to post a picture of the spreadsheet I am working on but this discussion board won't let me. I get this message: You are not allowed to create or update this content.

So I will try to understand your post above. How is a single sumifs statement contained in one cell able to create the entire spreadsheet called Table 2 in your example? How did the formula populate all of column A, for example?

Whenever I try to write a sumifs it only fills in one cell with a number. I can't figure out how you guys are getting that single statement to create an entire table.
Reply options

Link to this post

by SGIII,

SGIII Jul 12, 2016 9:02 AM in response to flashkube2
Level 6 (10,622 points)

Mac OS X

Jul 12, 2016 9:02 AM in response to flashkube2

You can learn about the basics of using Numbers in your menu at Help > Numbers Help and Help > Formulas and Functions Help.

You can learn about posting a screenshot in a post in Wayne's user tip.

SG

.
Reply options

Link to this post

Page 2

Q: Pivot/SUMIF Help

All replies Helpful answers

by Barry,

by flashkube2,

by SGIII,

All replies

Helpful answers