SUMIF Formula for Dashboard

Alexandra Reeder

I am creating a dashboard metrics sheet and would like a sum of the total $ amount closed based on a category of client. For example, in this case...

I would like to SUM the $ in the far right column IF the row contains "SVP" (as seen in the far left column). If it did it correctly based on the screenshot above, the cell should reflect $364,000 ($250,000+$114,000).

I'm doing this in a metrics sheet and linking to the referenced column above which is contained in another sheet, so in this case the range is showing as {Sheet - Sales Pipeline Range 5}. With that context, here is a screenshot of the formula I tried, and the screenshot showing the result as $0.

Any ideas? Thank you!

Seth Morth

Hello Alexandra,

It looks like you are using SUMIFS structure where the criterion is last. You will also need to specify a criterion, not just the location for it.

Try this:

=SUMIFS({Sheet - Sales Pipeline Range 5}, ="SVP", [Labels]@row)

Since it seems all the info is on the source sheet, you can also do it this way (I will show it as a SUMIF so you can see the difference)

=SUMIF({Sheet - Sales Pipeline Range_Label}, ="SVP", {Sheet - Sales Pipeline Range 5})

****You will have to create a new range, Sheet - Sales Pipeline Range_Label - BTW you can rename ranges to make life easier.

If the latter, would consider using a Sheet Summary to collect this, it could help you if you need to get the result into a Report in a more elegant fashion if the need arises.

I hope this helps!

Seth Morth

Hello Alexandra,

It looks like you are using SUMIFS structure where the criterion is last. You will also need to specify a criterion, not just the location for it.

Try this:

=SUMIFS({Sheet - Sales Pipeline Range 5}, ="SVP", [Labels]@row)

Since it seems all the info is on the source sheet, you can also do it this way (I will show it as a SUMIF so you can see the difference)

=SUMIF({Sheet - Sales Pipeline Range_Label}, ="SVP", {Sheet - Sales Pipeline Range 5})

****You will have to create a new range, Sheet - Sales Pipeline Range_Label - BTW you can rename ranges to make life easier.

If the latter, would consider using a Sheet Summary to collect this, it could help you if you need to get the result into a Report in a more elegant fashion if the need arises.

I hope this helps!

Alexandra Reeder

Hi Seth! The latter worked, thank you for all the tips. One (perhaps silly) follow-up question, is there an easy way to sum up multiple columns in the latter method? The criteria would remain the same. Thank you so much!

Seth Morth

Hello Alexandra,

Indeed there is; a Cross-Sheet Reference range can be multiple columns, select the ones you want by holding CTRL and LEfT+CLICKing on the column header. Below is an example.

Glad to hear you are getting this sorted. Well done!