Formula for multiple criteria on summing up columns

Hi, all! Does anyone know the formula for multiple criteria to be met before adding column values up? The data set example follows. I need to be able to total up 7 cost types if they are either billable or not, expense or capital, one-time or ongoing cost. The sums I need to integrate are (1) total billable capital one-time cost, (2) total non-billable capital one-time cost, (3) total billable expense one-time cost, and (4) total non-billable expense one-time cost. Your expertise shared is most appreciated.

Desiree


Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Desiree de los Reyes

    Hope you are fine, please try the following formulas:

    1- total billable capital one-time cost

    =IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group],
    @cell = "one-time cost", [Capital?]:[Capital?], @cell = "Yes", [Billable?]:[Billable?],
    @cell = "Yes"), "")
    

    2-total non-billable capital one-time cost

    =IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group],
     @cell = "one-time cost", [Capital?]:[Capital?], @cell = "Yes", [Billable?]:[Billable?],
     @cell = "No"), "")
    

    3-total billable expense one-time cost

    =IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group],
     @cell = "one-time cost", [Billable?]:[Billable?], @cell = "Yes", [Cost Type]:[Cost Type],
     CONTAINS("expense", @cell)), "")
    

    4-total non-billable expense one-time cost

    =IFERROR(SUMIFS([Cost Amount]:[Cost Amount], [Reporting Group]:[Reporting Group],
    @cell = "one-time cost", [Billable?]:[Billable?], @cell = "No", [Cost Type]:[Cost Type],
    CONTAINS("expense", @cell)), "")
    

    the following screenshot shows the result:


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Thank you so much, Bassam!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/26/21

    @Desiree de los Reyes

    You are welcome, and i will be happy to help you any time, Please help the Community by marking it as an ( Accepted Answer)

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!