Simple Sum question

jedelman
jedelman ✭✭✭
edited 06/08/23 in Formulas and Functions

This is a simple one but just having a hard time getting it to work.

I have multiple clients who purchase a variety of products per invoice. I need a Sum of the Product Type per Client. I've attached an image.

For example, I'd like to know Sum of Product 1 for Client 1. In this case the running total =5.

I realize I could do this in a report, but I need it as an equation (likely in a metric sheet) so I can add automations/notifications when a client might be reaching a threshold of Products purchased.


Best Answer

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓

    Hi @jedelman

    This formula should work:

    =SUMIFS([Total Per Invoice]:[Total Per Invoice], Client:Client, "Client 1", [Product/Item]:[Product/Item], "Product 1")

    If you are putting it on a metric sheet, the ranges will look a little different and you could save time by listing the clients in a column and the products in another column and using the "@row" function instead of the typing out "Client 1", for example. Then you could drag the formula down or make it a column formula depending on your use case.

    I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓

    Hi @jedelman

    This formula should work:

    =SUMIFS([Total Per Invoice]:[Total Per Invoice], Client:Client, "Client 1", [Product/Item]:[Product/Item], "Product 1")

    If you are putting it on a metric sheet, the ranges will look a little different and you could save time by listing the clients in a column and the products in another column and using the "@row" function instead of the typing out "Client 1", for example. Then you could drag the formula down or make it a column formula depending on your use case.

    I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

  • jedelman
    jedelman ✭✭✭
    edited 06/09/23

    Thanks @Matt Johnson

    Worked perfectly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!