SUMIFS with 2 criteria coming up zero

Hi!

This formula keeps returning a zero. I am trying to get it to count two different things in the same column (not sure why they were labeled differently) but when I get the formula plugged in it comes back with zero, when I know that's wrong.


I want it to count up the engineering hours based on the product lead, excluding things that have 2024 listed as a priority.


=SUMIFS([Engineering Hours - Enrich Team]:[Engineering Hours - Enrich Team], [Product Lead]:[Product Lead], "Madeline Hasegawa", [Product Lead]:[Product Lead], "Madeline Hasegawa +1", Priority:Priority, <>"2024")

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @AaronBogle

    In general, numbers shouldn't be enclosed in quotes. Smartsheet will convert these values into a textstring rather than a numeric value.

    =SUMIFS([Engineering Hours - Enrich Team]:[Engineering Hours - Enrich Team], [Product Lead]:[Product Lead], "Madeline Hasegawa", [Product Lead]:[Product Lead], "Madeline Hasegawa +1", Priority:Priority, <>2024)

    I assume there is a response in your [Product Lead] column exactly called "Madeline Hasegawa +1" and you were not trying to have smartsheet move one position up or down in a list.

    Will the above formula work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @AaronBogle

    In general, numbers shouldn't be enclosed in quotes. Smartsheet will convert these values into a textstring rather than a numeric value.

    =SUMIFS([Engineering Hours - Enrich Team]:[Engineering Hours - Enrich Team], [Product Lead]:[Product Lead], "Madeline Hasegawa", [Product Lead]:[Product Lead], "Madeline Hasegawa +1", Priority:Priority, <>2024)

    I assume there is a response in your [Product Lead] column exactly called "Madeline Hasegawa +1" and you were not trying to have smartsheet move one position up or down in a list.

    Will the above formula work for you?

    Kelly

  • Thank you for the help!

    Yes, I had a name listed with "+1" and was not trying to move anything. I keep having a problem where the formula returns an answer of "0", so I think I'll have to simplify things in order for it to work.

    I am still curious as to if I was correct in using <> to say exclude this criteria.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 11/10/23

    Hey @AaronBogle

    Sorry for the delay. Without an @mention, the email got lost in my inbox. You probably have already worked this out.

    Yes the <> is used correctly. However, because your criteria is one of only an exclusion, everything else is potentially fair game. You may need to add a criteria to make sure the date value is valid (ISDATE(@cell)), if you pulled the year from a date field, or the cell actually contains a number (ISNUMBER(@cell))

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!