You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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

Top-ranking reply

SGIII

Level 8

36,134 points

Posted on Apr 22, 2016 3:22 PM

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.

View in context

17 replies

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.

SGIII

Level 8

36,134 points

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.

Pivot/SUMIF Help