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.

User profile for user: Chris6861
Chris6861 Author
User level: Level 1
8 points

How to sum range of rows after Sort

Hello & Happy 2021,


I’ve read a few threads but can’t find answer to this. One thread suggested & I’ve looked at Numbers > File> New > Personal Finance > Personal Budget template, which I could use, but I’m trying to make my own formatting work easier for me.


Please see screenshot:


Column A = numbers from 1 to 13, which correspond to my personal categories, eg: 1= income, 2= Rent, 3 = Utilities, etc..


Column G = the amount in dollars that I would like to sum for each of my 13 categories.


I add income & expenses as they occur in date order, and then I sort the table. This results in my 13 categories sorting by their assigned numerical order first, and then by date order.


To sum each category, I’ve been using the Table’s Insert > SUM formula and manually retyping the start row and the end row of Column G, which often change each time I add data & then Sort. It’s always been tedious, and I’m trying to make it “automatic”.


Is there a formula in Numbers that would allow me to identify all rows whose cell value in Column A = a given number, and then SUM the values in Column G for all those same rows?


I tried IF, and SUMIF, but then got lost in “formula syntax error” responses.


Thanks,

Chris

P.s. I think I’m adding a screen shot but I can’t see it happening; sorry, 1st time posting a question.


MacBook Pro 13″, macOS 10.15

Posted on Jan 2, 2021 9:29 PM

Reply
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Posted on Jan 2, 2021 11:23 PM

Hi Chris,


SUMIF is the best choice here. I would suggest putting it in a separate table as is done in the Personal Budget template.


The new "Summary" table would have one Header row, and two columns.


Column A should contain a list, in whatever order you want, of the categories listed in column A of your Data table.


Column B of the (new) Summary table takes the SUMIF formula shown below, entered in B2, then filled down to the last row of that column.


B2: SUMIF(Data::A,A2,Data::G)


Here's a sample pair of tables, with the formula shown below them.

The formula will work with numerical categories as well as with 'named' categories.

I don't see any need to sort the Data table, but doing so should not affect the calculations as the SUMIF formula uses 'whole column' references for columns A and G of the Data table.


Regards,

Barry


@Ian,


Hi Ian, Thanks for the congrats.


I like your Screen shot instructions. Was considering posting mine here, but yours are straight to the point and far more succinct than mine. Well done!


Happy New Year

Barry


View in context

Similar questions

4 replies
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Jan 2, 2021 11:23 PM in response to Chris6861

Hi Chris,


SUMIF is the best choice here. I would suggest putting it in a separate table as is done in the Personal Budget template.


The new "Summary" table would have one Header row, and two columns.


Column A should contain a list, in whatever order you want, of the categories listed in column A of your Data table.


Column B of the (new) Summary table takes the SUMIF formula shown below, entered in B2, then filled down to the last row of that column.


B2: SUMIF(Data::A,A2,Data::G)


Here's a sample pair of tables, with the formula shown below them.

The formula will work with numerical categories as well as with 'named' categories.

I don't see any need to sort the Data table, but doing so should not affect the calculations as the SUMIF formula uses 'whole column' references for columns A and G of the Data table.


Regards,

Barry


@Ian,


Hi Ian, Thanks for the congrats.


I like your Screen shot instructions. Was considering posting mine here, but yours are straight to the point and far more succinct than mine. Well done!


Happy New Year

Barry


Reply
User profile for user: Yellowbox
Yellowbox
User level: Level 7
21,197 points

Jan 2, 2021 11:07 PM in response to Chris6861

Hi Chris,


Please reply with a screen shot of a small part of your screen (shift command 4). Full screen shots (shift command 3) are often difficult to read.


To take a screen shot:

  1. Click on the table to make it active so that is shows Row numbers and Column letters.
  2. Hold down the shift and command keys, then type 4. The cursor will change to crosshairs.

3. Drag over the relevant part of your screen then release the mouse/trackpad. You will hear a "camera shutter" sound. A screen shot will appear on your desktop. it will be named Screenshot with a date & time.

4. In a reply to a message, click on the "Mountains" icon in the Toolbar below your reply:


5. Choose File > Choose > Insert Image.


Remove any personal details before taking the screen shot.


Regards,

Ian.


Reply

How to sum range of rows after Sort

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