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")
Best 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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!