or Explore Discussions

# SUM with multiple conditions

06/30/21
Accepted

Hello Smartsheet Community,

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)

• 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

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

• 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})

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