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

Find more posts tagged with

Formulas

Accepted answers

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.

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

All comments

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.