SUM with multiple conditions

Hello Smartsheet Community,

Can someone please help me on this.

I am trying to get the sum of the entire column from the cross reference sheet and the formula =SUM({Sheet Name Range1},[Primary Column]10) is working fine as of now but I want to add 3 more conditions to meet before getting the total value from the Sheet Name Range1.

I tried somthing like this but it didn't work.

=SUM({Sheet Name Range2},[Primary Column]8,{Sheet Name Range3},[Primary Column]9,{Sheet Name Range4},[Primary Column]7, {Sheet Name Range1},[Primary Column]10)

Best Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    @Aatish Chaudhary ,

    Use SUMIFS() for multiple criteria.

    = SUMIFS( {Sheet Name Range1}, {Sheet Name Range1},[Primary Column]10,{Sheet Name Range2},[Primary Column]8,{Sheet Name Range3},[Primary Column]9,{Sheet Name Range4},[Primary Column]7 )

    Here's the formula formatted for easier reading:

    = SUMIFS({Sheet Name Range1}
    	,{Sheet Name Range1},[Primary Column]10
    	,{Sheet Name Range2},[Primary Column]8
    	,{Sheet Name Range3},[Primary Column]9
    	,{Sheet Name Range4},[Primary Column]7
    	)
    

    Here is the list of Smartsheet functions and documentation.

    https://help.smartsheet.com/functions

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, Aatish

    Your formula below does not work because you're using SUMIF(). The SUMIF() function can contain only one criterion. Your formula contains more than 3.

    =SUMIF({Sheet Name Range2},[Primary Column]8,{Sheet Name Range3},[Primary Column]9,{Sheet Name Range4},[Primary Column]7, {Sheet Name Range1})

    Since you're evaluating multiple columns before performing a SUM() function, you'll need to use SUMIFS(). The syntax for this function is,

    SUMIFS( range , criterion_range1 , criterion1 , [ criterion_range2 , ​criterion2 , ​... ])

    "range" represents the range that you are summing.

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    @Aatish Chaudhary ,

    Use SUMIFS() for multiple criteria.

    = SUMIFS( {Sheet Name Range1}, {Sheet Name Range1},[Primary Column]10,{Sheet Name Range2},[Primary Column]8,{Sheet Name Range3},[Primary Column]9,{Sheet Name Range4},[Primary Column]7 )

    Here's the formula formatted for easier reading:

    = SUMIFS({Sheet Name Range1}
    	,{Sheet Name Range1},[Primary Column]10
    	,{Sheet Name Range2},[Primary Column]8
    	,{Sheet Name Range3},[Primary Column]9
    	,{Sheet Name Range4},[Primary Column]7
    	)
    

    Here is the list of Smartsheet functions and documentation.

    https://help.smartsheet.com/functions

  • I just realized the earlier statement was confusing. So this is the formula I am using for now to get the value from the another sheet column after meeting the one condition =SUMIF({Sheet Name Range2}, [Primary Column]8, {Sheet Name Range 1}). I need help to add 2 more conditions in the same formula.

    I tried the below one but its not working after adding tow more condition.

    =SUMIF({Sheet Name Range2},[Primary Column]8,{Sheet Name Range3},[Primary Column]9,{Sheet Name Range4},[Primary Column]7, {Sheet Name Range1})

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, Aatish

    Your formula below does not work because you're using SUMIF(). The SUMIF() function can contain only one criterion. Your formula contains more than 3.

    =SUMIF({Sheet Name Range2},[Primary Column]8,{Sheet Name Range3},[Primary Column]9,{Sheet Name Range4},[Primary Column]7, {Sheet Name Range1})

    Since you're evaluating multiple columns before performing a SUM() function, you'll need to use SUMIFS(). The syntax for this function is,

    SUMIFS( range , criterion_range1 , criterion1 , [ criterion_range2 , ​criterion2 , ​... ])

    "range" represents the range that you are summing.

  • Thank you! This worked for me.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!