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 14. 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 / NonRetail Helper Column]@row:[Resources Required  Helper Column]@row, [Retail / NonRetail 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?
Best Answer

@GHustad1 It's easiest to check for blank cells in the range, then sum if there are none, like this:
=IF(COUNTIF([Retail / NonRetail Helper Column]@row:[Resources Required  Helper Column]@row, ISBLANK(@cell)) = 0, SUM([Retail / NonRetail 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.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Answers

@GHustad1 It's easiest to check for blank cells in the range, then sum if there are none, like this:
=IF(COUNTIF([Retail / NonRetail Helper Column]@row:[Resources Required  Helper Column]@row, ISBLANK(@cell)) = 0, SUM([Retail / NonRetail 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.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions

Amazing! Thanks so much.

@GHustad1 You're welcome!
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Help Article Resources
Categories
Check out the Formula Handbook template!