I have two ranges of cells of equal length (the calls are non-adjacent).
Some values are missing/blank.
I need two count how many of the cell pairs have a numerical value in them.
for example, if the pairs are:
1, 2
2, 2
3, 0
2, –
-, 3
-,-
the count should be 5 (pairs 1-3 have both values, pairs 4-5 have one value, pair 6 has two blanks and is not counted).
Is there a way do this in ONE formula, without any helper cells ?
Another constraint is that it should run on Excel 2019 or earlier…
My first thought was to use something like:
=COUNTIFS(ISNUMBER(A1:F1),TRUE,ISNUMBER(A3:F3),TRUE)
However, this raises an error, and is not even accepted as a valid formula…
>Solution :
=SUM(IF((ISNUMBER(A1:A8)+ISNUMBER(B1:B8))>0,1,0))
Result:
Keep in mind that date formatted for example as "dd-mmm" would give false positive if it is not considered as number in your case.