How to sum range of rows after Sort

Question

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

Answer 1

Best reply

Barry

Level 9

59,885 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

Answer 2

Yellowbox

Level 7

20,453 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:

Click on the table to make it active so that is shows Row numbers and Column letters.
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

Answer 3

Chris6861 Author

Level 1

8 points

Jan 3, 2021 9:07 AM in response to Yellowbox

Ian,

thanks for helpful how-to.

Barry’s reply has allowed me see how to solve my issue, so I’ll not need to send screenshots.

Thanks again for your speedy reply,

Chris

Reply

Answer 4

Chris6861 Author

Level 1

8 points

Jan 3, 2021 9:12 AM in response to Barry

Hi Barry,

Thanks for your speedy reply, I understand how to follow-on with the SumIf formula now, and it solves my problem.

Thanks again,

Chris

Reply

How to sum range of rows after Sort

Similar questions