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
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









