Excel Sumifs alternative criteria to "<>": How to check for non-blanks when the criteria range has formulas which can output nothing?

0
votes

I've run into a strange issue.

When checking a criteria range against "<>", this does not seem to capture cells that contain formulas which output nothing.

For example, if a cell has the formula ="", then the "<>" criteria recognizes the cell contains a formula. So even though the string value is nothing, it's counted anyway.

enter image description here

The desired result for "<>" on the above example would be 0, but since the cell contains a formula (=""), the value for that row is counted.

Is there a criteria alternative to "<>" which can capture non-blanks, even if it's the result of a formula?

I know I could take a sum, and subtract the result of SUMIFS(A2:A3, C2:C3, ""). However in practice I'm writing extremely complex formulas (multiple lines for a single formula), so to apply this method would be to double the size of my already huge formulas, and throw readability out the window. It would be much simpler if I could simply add a functional criteria to my existing sumifs formula.

2
excelexcel-formulasumifs
You can test for at least one actual character using "?*" – Rory

2 Answers

1
votes

You could use a SUMPRODUCT:

=SUMPRODUCT(A2:A3,LEN(B2:B3)>0)
0
votes

I just figured out a solution;

Instead of having the formula in the criteria range output nothing, have it output a single space:

=" "

Then, you can have the sumifs search against " " and "<> "