SUMIF or SUM for cross-sheet formula

System

This discussion was created from comments split from: SUMIF - Referencing another sheet.

Cindi Meche

Hi,

I'm referencing another sheet and trying to get the total cost of a column if another column lists a specific word. However, I am getting the full total of the column (not just for the word) with:

=SUM({Social Media Data 2021 Range 1}, "Maternity", {Social Media Data 2021 Range 2})

Or I get #INCORRECT ARGUMENT if I use SUMIF:

=SUMIF({Social Media Data 2021 Range 1}, {Social Media Data 2021 Range 2}, "Maternity")

Can someone please help me fix my query?

Jason Duryea

The correct syntax for SUMIF is below.

SUMIF( range, criterion, [ sum_range])

• range — The group of cells to evaluate.
• criterion — The condition that defines which numbers to add (for example: 15, "Hello World!", >25).
• sum_range —[optional] The group of cells to add, if different from the range.

In your case you could do this a couple of different ways. Assuming the Range 2 column has the value to sum and Range 1 has the value of “Maternity”.

=SUMIF({Social Media Data 2021 Range 2}, {Social Media Data 2021 Range 1}, “Maternity”)

You could also use =SUM(COLLECT({Social Media Data 2021 Range 2}, {Social Media Data 2021 Range 1}, “Maternity”))