SUM with multiple conditions

Aatish Chaudhary

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)

Toufong Vang

@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

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.

Toufong Vang

@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

Aatish Chaudhary

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

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.

Aatish Chaudhary

Thank you! This worked for me.