SUMIF

Question

Level 1

12 points

SUMIF

Is it possible for the condition of a SUMIF to refer to another table on the same sheet? It doesn't seem to work!

I have a table with 80 or so rows of numerical data. When I write a few rows on the same table using SUMIF to total up amounts in a column that meet certain criteria in another column then it works fine. However, when I write the same formula in another table referring to my data table, it doesn't seem to work. Not a syntax error but just seems to ignore the 'condition' part of the statement.

Any ideas would be much appreciated, thank you.

Mac mini (M1, 2020)

Posted on Apr 28, 2023 4:24 AM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,197 points

Apr 28, 2023 8:40 PM in response to buro46_Pete

Hi Pete,

Glad to help. Yes, you can specify a range. I was making use of Numbers' ability to refer to all the body rows of a table. It ignores Header Rows (Row 1 in my example) and Footer Rows (not present in my example). Take a look in Template Chooser to see excellent examples of the Numbers approach (several small tables, each with a purpose).

Regards,

Ian.

Reply

Answer 2

Yellowbox

Level 7

21,197 points

Apr 28, 2023 5:19 AM in response to buro46_Pete

Hi buro46_Pete,

See if this works for you.

Formula in the Totals table, B2: SUMIF(Table 1::A,A2,Table 1::B)

Fill down.

Regards,

Ian.

Reply

Answer 3

buro46_Pete Author

Level 1

12 points

Apr 28, 2023 6:10 AM in response to Yellowbox

Many thanks Ian, you have saved the day! I was just going to send you a copy of the formula that I used, which is similar to the one you suggested, only I had specified a precise range.

When I copied and pasted I realised I had my columns the wrong way round, ie instead of SUMIF(test-values, condition, sum-values) I effectively had SUMIF(sum-values, condition, test-values) so no wonder it didn't work!

Anyway, many thanks again for your time.

Reply

Answer 4

buro46_Pete Author

Level 1

12 points

Apr 29, 2023 2:15 AM in response to Yellowbox

Thank you Ian, that's a handy tip that I didn't know about. In fact I could use that in this current case as the range I am choosing is all rows except the first, header row.

Kind regards,

Peter

Reply

SUMIF

Similar questions