SUMIF help please

laura.sandoval

I'm having some trouble with the SUMIF formula for one of my sheets. I have a metrics sheet for my dashboard and I'd like to sum up all of the number of patients per column, based on their assigned color group.

I keep getting #unparseable :(

=SUMIF([Total #]10:[Total #]72, "Orange", [Color Group]10:[Color Group]72)

Help please!

Corey W.

Hello,

You will want to swap the total range with the color group range. SUMIF has the criteria range first, then the criteria, and the sum range last. Which is admittedly a little odd.

Nic Larsen

Unless it's not in your screenshot, it doesn't appear that you have a column called Total #, so that would also need to be updated.

JamesB

@laura.sandoval

You need to adjust the order of operations in your formula. You also have to reference the column name.

SUMIF is the range to look at, then the criteria in that range, then where the sum_range is.

Try this…

=SUMIF([Color Group]10:[Color Group]72,"Orange",[Total # of all patients]10:[Total # of all patients]72)

laura.sandoval

@Corey W. thank you! I switched it but still not working…

@Nic Larsen ah good call, I renamed the columns and rearranged a bit. I'm a little stumped as to how to best make this sheet work as a metrics sheet for the dashboard. This is the layout (first screenshot), the top two sections are the total patient count and then patients assigned to each color group. Underneath that is a parent/child section with all the patients nested into that and then the columns for each.

I need to get a sum of all the patients assigned to blue then to the orange…in the second screenshot you can see the formula I have:

Nic Larsen

It still looks to me that there is not a Total # column and that it should read (assuming that's the one you are referencing) [Total # of all patients]10:[Total # of all patients]72 in your formula. But maybe you already updated that after the screenshots.

Jake Gustafson

Agreed with @Nic Larsen on the column names. Once you have valid references, then the formula bar will color code the references giving a visual indication of a valid formula.

Side note, since you have child rows that you're totaling, you could look at adding a helper column to see what level of hierarchy each row is at and sumifs on only your values meeting that criterion (i.e., ancestors = 1) to avoid hard coding specific rows into your formulas. That may not be an issue for you if the sheet is pretty static.

Also, you could look at using a Sheet Summary field for your parameters (i.e., Orange#) to avoid having to adjust individual values in each cell formula and instead manage it from the Sheet Summary panel.

SUMIF vs. SUMIFS can sometimes be a battle with the order of parameters mixed around. Hope all of this helps you on your journey.

laura.sandoval

Thank you all! I moved around the columns and tables so I could use the SUMIF for the whole column (and use the proper title). It was tough to figure out with the nested rows, all figured out!