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
-
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.
-
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
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!