SUMIF Formula for Dashboard

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...

Screen Shot 2022-06-17 at 1.19.33 PM.png

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!

Find more posts tagged with

Accepted answers

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!

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.