Sumifs formula only if all columns contain a value

I have sheet that is "scoring" projects across 6 criteria. The criteria all have numeric values associated with them from 1-4. I want to sum the values for all the criteria, but ONLY IF all columns have a value. Meaning, if someone fails to score one of the criteria, I want the sum column to be blank vs it summing the other 5 criteria.

The formula I created is this: =SUMIFS([Retail / Non-Retail Helper Column]@row:[Resources Required - Helper Column]@row, [Retail / Non-Retail Helper Column]@row, <>"", [Impact to Business - Helper Column]@row, <>"", [Alignment w/ Strat Priority - Helper Column]@row, <>"", [Leadership Engagement - Helper Column]@row, <>"", [LOE - Helper Column]@row, <>"", [Resources Required - Helper Column]@row, <>"")

It is resulting in #INCORRECT ARGUMENT SET and I've tried to rewrite it a number of ways and I can't sort it out. Any thoughts?

Find more posts tagged with

Accepted answers

Darren Mullen

@GHustad1 It's easiest to check for blank cells in the range, then sum if there are none, like this:

=IF(COUNTIF([Retail / Non-Retail Helper Column]@row:[Resources Required - Helper Column]@row, ISBLANK(@cell)) = 0, SUM([Retail / Non-Retail Helper Column]@row:[Resources Required - Helper Column]@row), "")

The formula counts the number if blank cells in the range, if there is at least one, the count will NOT equal 0, and it will insert "", else if there are values in all cells, it sums the range.

All comments

Darren Mullen

@GHustad1 It's easiest to check for blank cells in the range, then sum if there are none, like this:

The formula counts the number if blank cells in the range, if there is at least one, the count will NOT equal 0, and it will insert "", else if there are values in all cells, it sums the range.

GHustad1

Amazing! Thanks so much.

Darren Mullen

@GHustad1 You're welcome!

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.