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.

Numbers for Mac

I have a spreadsheet where I am using several pop-up menus to fill cells. Is there a way to automatically summarize my selections on a separate sheet/table?



Posted on Nov 20, 2022 2:00 PM

Reply
Question marked as Top-ranking reply

Posted on Nov 20, 2022 5:10 PM

The formulas shown below the Summary table will do what you want:

Enter the first formula in cell A2 of Summary. Set the tokens by clicking the first token, then clicking the column reference tab for column B of the Items table.

Repeat for the second token.

Repeat for the third token, this time the column reference tab for column A.

Click on the fourth token (If not found) and enter "-".

The last two tokens should be left in place.


The second formula is placed in cell B2 of Summary. The only difference between them is that the third token is addressed to column B of Items.


A3 and B3 of summary get the same pair of formulas, with the source table changed from Items to Swag.


A4 and B4 of summary get the same pair of formulas, with the source table changed from Items to Widgets.


Results for the data provided in your example are displayed in the Summary table shown.


Note: I took the easy route of keeping the Item, Swag and Widget numbers in the same order. The order on the Data tables does not affect the results.


Regards,

Barry


Regards,

Barry



4 replies
Question marked as Top-ranking reply

Nov 20, 2022 5:10 PM in response to snivo

The formulas shown below the Summary table will do what you want:

Enter the first formula in cell A2 of Summary. Set the tokens by clicking the first token, then clicking the column reference tab for column B of the Items table.

Repeat for the second token.

Repeat for the third token, this time the column reference tab for column A.

Click on the fourth token (If not found) and enter "-".

The last two tokens should be left in place.


The second formula is placed in cell B2 of Summary. The only difference between them is that the third token is addressed to column B of Items.


A3 and B3 of summary get the same pair of formulas, with the source table changed from Items to Swag.


A4 and B4 of summary get the same pair of formulas, with the source table changed from Items to Widgets.


Results for the data provided in your example are displayed in the Summary table shown.


Note: I took the easy route of keeping the Item, Swag and Widget numbers in the same order. The order on the Data tables does not affect the results.


Regards,

Barry


Regards,

Barry



Nov 20, 2022 7:22 PM in response to snivo

It's almost always more efficient to keep your data in one table rather than scatter them around separate tables.


That enables you to extract summaries with simple formulas like this:




Or no formulas at all! (via a Pivot Table):




Pivot Tables sound complicated. But they're very simple to use. Easier than entering formulas. Click in table with the data, choose Organize > Create Pivot Table > On Current Sheet from the menu and drag fields in the Pivot Option pane down into the boxes below as shown in the screenshot.


Note that with a few clicks you can use a filter on the data table to show just Items, or just Swag, etc.



SG



Numbers for Mac

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.