SUMIFS and blank cells

0

One thing that isn’t well documented is what happens to Excel is there is a blank in a cell.
By experimentation it turns out that the wildcard “” does *not match a blank cell, so you need to make sure that you have something in every cell if you want to match it. 
Interestingly, stackoverflow says that you can match a non-blank cell with SUMIFS(S:S, C:C, "<>") but it isn’t clear how you match an actual blank cell. Seems like there is no wya to do this.
As another aside, if you cell has a wildcard, how do you match it?
Also what happens if you want to match an actual asterick or question mark in a cell, apparently, the tilde let’s you do that with SUMIFS(S:S, C:C, "~?") or SUMIFS(S:S, "~*") if that makes sense. I usual think of the tilde as a negation and would have though backslash would do it. 
As an aside, you can do these kind of and queries by just duplicating the ranges, so you can use this for inclusion test so for instance to find cells that a >100 and <500, you can do SUMIF(S:S, S:S, "&lt;100", S:S, "&lt;500")

Related Posts

© All Right Reserved