SUMIFS with 2 criteria coming up zero

AaronBogle

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

Kelly Moore

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

Kelly Moore

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

AaronBogle

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

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