SUMIFS keeps returning 0

I'm trying to display a sum of the column Total Funds Available when Status = Apps Open, and Value Score = 5. Here is the formula I have:

=SUMIFS([Total Funds Available]:[Total Funds Available], Status:Status, ="Apps Open", [Value Score]:[Value Score], ="5")

There are rows that meet these conditions and should display a total, but no matter what I do, I'm getting a result of 0. I tried it without the Value Score portion and it worked, so something is breaking down there. Value Score is a column formula that averages several different columns and is set to round to the nearest multiple of 0.5. I tried putting VALUE() around the column formula and that didn't work. I also tried creating a helper column for =VALUE([Value Score]@row ) and putting that in the SUMIFS formula instead, and that didn't work either.

What am I missing? Is there some reason SUMIFS doesn't work when referencing a column formula? What else can I try?

Find more posts tagged with

Formulas

SumIfs

Accepted answers

All comments

ker9

@ekdarnell

Try this - remove the equal sign in front of "Apps Open" and remove the quotes around the number 5

=SUMIFS([Total Funds Available]:[Total Funds Available], Status:Status, "Apps Open", [Value Score]:[Value Score], =5)

Paul Newcome

If it works without the [Value Score] range/criteria set, and that column is being output as a number (indicated by rounding and averaging), you will need to remove the quotes from around the 5. Putting quotes around a number turns it into a text string that simply looks like a number. Since a text value does not equal a number value (different data types), you won't have any rows that are matching there.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.