Sumif getting 0

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")



Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    Andrea Westrich ✭✭✭✭✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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.
    Genevieve P. Employee Admin

    Did @Bassam Khalil's formula work for you?

  • Andrea Westrich
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!