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)

Find more posts tagged with

Accepted answers

TVang

@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

TVang

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.

All comments

TVang

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

TVang

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.