SUMIF Formula for Dashboard

Options

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!

Best Answer

  • Seth Morth
    Seth Morth ✭✭✭✭✭
    Answer ✓
    Options

    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!

Answers

  • Seth Morth
    Seth Morth ✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    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
    Seth Morth ✭✭✭✭✭
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!