Sumif getting 0

Andrea Westrich

This one should be a no-brainer, but I have "formula block".

I'm trying to run a SUMIFS on the test sheet below to sum the Sales price if the Year of Contract is 2021 and the Split Category is CSA Assisted Lead. The Year of contract column comes from the YEAR function run on the Contract Date.

My formula came out to 0, so I broke it down to sum one condition at a time, but I'm still getting 0. Here is the formula to sum if one condition is true. Why does this produce a 0?

=SUMIF([Sales Price]:[Sales Price], [Split Category]:[Split Category] = "CSA Assisted Lead")

Bassam Khalil

Hi @Andrea Westrich

Hope you are fine, please try the following formula:

=IFERROR(SUMIFS([Sales Price]:[Sales Price], [Split Category]:[Split Category], 
@cell = "CSA Assisted Lead", [Year of Contract]:[Year of Contract], @cell = 2021), "")

The Following screenshot shows the result:

Bassam Khalil

Hi @Andrea Westrich

Hope you are fine, please try the following formula:

=IFERROR(SUMIFS([Sales Price]:[Sales Price], [Split Category]:[Split Category], 
@cell = "CSA Assisted Lead", [Year of Contract]:[Year of Contract], @cell = 2021), "")

The Following screenshot shows the result:

Genevieve P.

Hi @Andrea Westrich

The SUMIF function defines Criteria with a Comma, versus using an = sign like in an IF statement.

Also, if you're using a singular SUMIF function (versus SUMIFS, plural), you want to have the column to SUM at the end of the formula.

This is the structure of a SUMIF:

=SUMIF([Criteria Column]:[Criteria Column], "Criteria", [Column to Sum]:[Column to Sum])

So in your instance, try this:

=SUMIF([Split Category]:[Split Category], "CSA Assisted Lead", [Sales Price]:[Sales Price])

Cheers!

Genevieve

Andrea Westrich

Thank you @Genevieve P. that is incredibly helpful!! What is the structure of SUMIFS for single criteria vs multiple?

Genevieve P.

https://community.smartsheet.com/discussion/comment/299499#Comment_299499

The SUMIFS is opposite: you'll list the Column to SUM first. Then list each column with criteria, comma, criteria.

Ex:

=SUMIFS([Column to Sum]:[Column to Sum], [1st Criteria Column]:[1st Criteria Column], "1st Criteria", [2nd Criteria Column]:[2nd Criteria Column], "2nd Criteria")

See: https://help.smartsheet.com/function/sumifs

If you're still getting 0, can you paste your full formula in? I wonder if it's unable to find the Match for some reason.

Genevieve P.

Did @Bassam Khalil's formula work for you?

Andrea Westrich

@Genevieve P. Your single formula worked. When I tried to use the SUMIFS for multiple criteria I'm getting Invalid data type with the following formula.

=SUMIFS([Sales Price]:[Sales Price], [Split Category]:[Split Category], "CSA Assisted Lead", [Year of Contract]:[Year of Contract], "2021")

@Bassam Khalil formula gives me a blank cell, which leads me to believe there is some type of error with the criteria.

I did more investigation: the single SUMIF formula for the Split Category works. However, it does not for the Year of contract does not and the reason I'm getting the Invalid Data Type errors is that some cells in that column have that error due to not having a date from another sheet. I cannot correct those, they self-correct when data is updated.

Giving this information, shouldn't @Bassam Khalil formula work?

Genevieve P.

Hi @Andrea Westrich

This is great information, thank you!

If a formula references a column that contains an error, it will automatically roll up that error into this new formula, even if the column contains the criteria you're looking for.

The way to resolve this is to put Bassam's IFERROR statement around your YEAR formula, isntead!

What formula are you using to grab the year?

Try this:

IFERROR(Year Formula, "")

Then your SUMIFS formula should work without an error since the column it's referencing has correct values.

Cheers,

Genevieve

Andrea Westrich

@Genevieve P. and @Bassam Khalil thank you both SO much! Because I cannot change the Year of Contract column for other linked reasons, I created a new hidden column that uses Bassam's Iferror to provide a year column without errors.

Thank you @Genevieve P. for so thoroughly explaining the nuances of both Sumif and Sumifs! That knowledge will help me in the future.

Thanks to both of you, I have a working and accurate formula!!

Genevieve P.

Wonderful! I'm glad that you got it working. 🙂