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

Level 1

8 points

Need Help Summing Data

Previously I had started this discussuion on or about 11/12/18 and it was anseweredby Barry. Since then I have had major issues in signing back into the community and this discussion so I had to create a new Apple ID and community user name.

I want to ask for further help on by initial request. Thje following was Barry's reply.

Yes.

But why have you spread the recording of expenditures across so many columns?

You really need only four columns to record this data: Date, Vendor, Description, Amount.

Instead of summing expenditures to each vendor in a cell in a footer row, use a separate Summary table to collect the amounts in each category (description) for each vendor.

The Personal Budget Template, found in the Template Chooser can provide you with an example of summarizing by categories. With Numbers running, go to thr File menu, press and hold the option key as you choose New from Template Chooser, then select Personal Budget.

All transactions are recorded in the Transactions table. They are summarized in the Summary by Category table on a separate sheet.

The summary sheet uses SUMIF formulas. Your Summary table will use SUMIFS, as you want to summarize on two categories—vendor and description.

After examining the Personal Budget template, take a look at SUMIFS in the Function Browser. To open the browser, type an = in any empty cell in a table. Enter SUM in the search box of th browser, then select SUMIFS in the short list to view the information about that function in te space blow the list.

After checking the template and the Function Browser, post a reply here with any questions that arise.

Regards,

Barry"

I took his advise and accessed the Personal Budget Template. I then searched and found this Which I have adopted but I would like to have this modified to a complete annual Budget that would include Income as opposed to just a monthly expense tracker. Can this be done? In the past I have been using this http://www.vertex42.com/ExcelTemplates/personal-budget-spreadsheet.html I prefer the new one as it sums uprthe monthly expenses by catagory and I don't have to manually enter all the entries manually.

Nov 2, 2017 7:43 AM in response to wyoming307girl

A while back I expanded the Personal Budget template along the lines you describe. You can download it here (Dropbox download).

Note that there is only one transactions sheet (rather than the 12 you describe). In general you will find that inputing and keeping your data together in one table is more efficient in Numbers.

iMac, macOS High Sierra (10.13.6)

Posted on Nov 16, 2018 1:26 AM

Best reply

Barry

Level 9

59,861 points

Posted on Dec 6, 2018 6:24 PM

HI d'

"Another question. Using the transaction table how would I come up with yearly expendurtures for each merchant?

It would be nice to have the results update as a new transaction for each merchant was entered."

In your Transaction table, Amounts are listed in column D, Merchants are listed in column E.

In a Summary table, list the Merchants in column A, starting at A2, with one entry for each merchant for whom you want a total. n column B, use SUMIF to sum all values in column D of Transactions that are tagged (in column E of that table) with the name of the merchant being summed in 'his row'.

B2: SUMIF(TRANSACTIONS::E,A2,TRANSACTIONS::D)

Fill the formula down the rest of column B.