Need Help Summing Data

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.

"

Need Help Summing Data

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.

SG

iMac, macOS High Sierra (10.13.6)

Posted on Nov 16, 2018 1:26 AM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Dec 6, 2018 6:24 PM in response to dtryon9981

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.

Regards,

Barry

Reply

Answer 2

dtryon9981 Author

Level 1

8 points

Nov 25, 2018 5:07 AM in response to dtryon9981

Responding to SGIII's suggestion.

Previously I have been using this for an annual budget

https://www.vertex42.com/ExcelTemplates/personal-budget-spreadsheet.html

I have now found the Personal Budget in the template section of Numbers.

I don't know of a way to alpha sort the catagory drop down list as it grows other than dragging the items around.

As I modified the template to suit me I created an additional table for Catagories. It is easy to add catagoriesin the table as required.

M question is how can I link that new table to the dropdown box as shown here?

Reply

Answer 3

SGIII

Level 8

35,276 points

Nov 25, 2018 7:07 AM in response to dtryon9981

Are you hoping to have your Pop-Up Menu update automatically when you change the Categories table? If so, Numbers (unlike Excel) doesn't have that feature.

However, it's quite easy to edit a Pop-Up Menu, dragging items up and down, etc.

SG

Reply

Answer 4

dtryon9981 Author

Level 1

8 points

Nov 25, 2018 11:24 AM in response to SGIII

That is not completely clear to me. I selected the desired fields and then set the Data Format to pop-up, copied it, created another column "F" and pasted it but it does not appear to act as a pop-up when I add a transaction. Can you issustrate with some screen shots and further instructions?

Reply

Answer 5

dtryon9981 Author

Level 1

8 points

Dec 2, 2018 6:48 AM in response to SGIII

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.

Reply

Answer 6

Barry

Level 9

59,861 points

Feb 15, 2019 3:02 PM in response to odoom1122

ODOOM:

You have posted your question (three times now) as a reply in a discussion of an entirely different issue.

You have posted this question in the Numbers for Mac community.

Your question does not involve Apple's spreadsheet application, Numbers.

Your question does not involve the use of a Mac computer.

You are lost.

Here are directions to a better place to post your question:

Go to the top right corner of this webpage. Click Support Communities.

(Or click this link, which goes to the same place: Official Apple Support Community )

On the Welcome Page, scroll down and click on the iPhone icon in the row of icons below the smiling faces.

On the next page, click Using iPhone.

Then use the "Search or ask a question" box to compose your question.

These are user-to- user communities, so there may be some delay before you receive a reponse. Be patient.

Regards,

Barry

Reply

Answer 7

dtryon9981 Author

Level 1

8 points

Feb 17, 2019 11:34 AM in response to SGIII

SG,

I have given up on my previous searching question and will ask for help in approaching it from a different perspective. Looking at the screenshot below is there a means of searching the merchant column "D" in this instance Trader Joes and having the search present all instances of Trader Joes along with

Date Column A
Amount Column B
Merchant Column D

Reply

Answer 8

Barry

Level 9

59,861 points

Dec 15, 2018 9:52 AM in response to dtryon9981

Will both columns in Summary by Merchant populate themselves as transactions are entered in the Transactions table?

Column B will update with each new transaction with a listed merchant.

Column A will require manual updating to add NEW merchants to the list.

If you are using pop-up menu cells in the merchant column of Transactions, you can:

Add a new merchant to the next row, replacing the pop-up in that cell.
Select that cell and the one above it (which still contains the current pop-up menu).
With both cells selected, use the Format Inspector to set data format to Pop-up Menu.
The new merchant will be added to the menu in the first cell, and the full menu will be created in the second.
- Both cells will retain their current settings.
Select the last cell, Copy, then Past it into the next cell in column A of Summary.

Note: You could create the new popup-menu, using the steps above, in the Summary table, then Paste it into the next available row in Transactions, and Fill it down from there.

Regards,

Barry

Reply

Answer 9

Barry

Level 9

59,861 points

Jan 14, 2019 12:28 AM in response to dtryon9981

Hi d'

That's the way Numbers works.

See my December 15 post above regarding adding a new menu item, then updating the new cell to contain the full menu.

After that,

set the new menu cell to 'none',
Copy,
Select the new menu cell and all cells below it in the same column,
Paste
Reset the new cell to the merchant you want, and continue.

With the copy of the cell still on the clipboard:

Go to the other table, where you want menus with an identical list.
Select the cells where you want the new menu (Rows that you have not yet used, as the paste will replace whatever is currently in the selected cells).
Paste.

Regards,

Barry

Reply

Answer 10

dtryon9981 Author

Level 1

8 points

Jan 3, 2019 8:17 PM in response to SGIII

I have found that as I an entering transaction I have a new merchant that I have not thout of including in the pop-up menu initially. When I add a new merchant the merchant is only available in the cell or line where I added it. Is there a way to make all additional global?

I have the same situation with the Category column.

Reply

Answer 11

dtryon9981 Author

Level 1

8 points

Jan 6, 2019 10:09 AM in response to Barry

Barry,

I have the issue that the context (Available Names) in the Merchant col on the LH table and the Merchant col in the RH table do not match. Is there a way that both columns can be populated by the same pop-up menu? So that when I add a name to the Merchant pop-up on the LH (Transactions) tbl I only have to enter the new merchant name one time and the Merchant pop-up on the RH would match? Currently I have to enter the new name in the pop-up field on both tabls.

Reply

Answer 12

Yellowbox

Level 7

20,383 points

Jan 14, 2019 12:57 AM in response to dtryon9981

Hi Dtryon,

Current Merchants in Pop-Up Menus:

Add a new Merchant (E) in a blank row and make that a Pop-Up Menu

Select all the Pop-Up Menus and Merge Menu Items

Cells maintain their chosen setting and all contain the full list of merchants.

Regards,

Ian.

Hi Barry,

Good to see you back. We have been holding the fort awaiting your return.

Regards,

Ian.

Reply

Answer 13

dtryon9981 Author

Level 1

8 points

Feb 8, 2019 9:23 AM in response to SGIII

Evidently I am not doing something the right way or in the right spot. If I go here ad select Quick filter on Food City you can see in the bottom screenshot that Food city appears (7) times. By selecting Food City the entire table disappears.

Reply

Answer 14

dtryon9981 Author

Level 1

8 points

Feb 10, 2019 11:18 AM in response to Barry

Anything I pick from the shown list gives the same results. It doesn't matter what column I were to use the results are the same, the entire table disappears.

There is a column not shown in this screenshot on the far right of the other Merchant table that contains the $$$ totals. I am wanting the entries in the Transactions table to be shown that represent the the $$$'s shown for any Merchant that represents those total $$$'s.

Reply

Answer 15

SGIII

Level 8

35,276 points

Feb 10, 2019 9:00 PM in response to dtryon9981

Is it possible you have another filter set that in combination with the Quick Filters filters out all the rows?

Go here and remove (actually remove by clicking the trash cans, don't just set the switch to off) all filters. Then try the Quick Filter again.

SG

Reply