formulasgoogle sheets
According to COUNTIF the documentation,
criterioncan contain wildcards including?to match any single character or*to match zero or more contiguous characters.
But this is incorrect because * doesn't match an empty cell.
How can I match any value, including blank?
You can check whether a cell is empty or not by using the following formula:
=if(C2="";"The cell is empty";"The cell is not empty")
Try the following solution.
=ARRAYFORMULA(TRIM(SPLIT(JOIN(" ,", A1:A4,C1:C4), ",")))
You already showed us that the SPLIT function ignores empty cells, therefore I choose this string as delimiter: " ,". The intermediate result of the JOIN function, looks like this:
I choose the delimiter for the SPLIT function to be this: ",". The intermediate result of the SPLIT function, looks like this:
Now, however, we have to deal with the extra added white spaces. Therefore I used the TRIM function, to remove all trailing or leading white spaces (in combination with the ARRAYFORMULA). Compare the LEN values of each intermediate result.
I've created an example file for you: SPLIT to preserve blank cells
Best Answer
Using a
*doesn't work because an empty cell does not have zero characters; it's basically 'undefined'. It does not match anyCOUNTIFcriterion you can think of. This is how it's intended to be used:Test*matches both 'Test' and 'Test2':To count empty cells, you'll need
COUNTBLANK: