How to count non blank cells when a certain range has written values

April 19, 2024

Simple question: How do I count how many non-blank cells from column A also have at least one "X" to their right?

A	B	C	D	E
data 01	X
data 02		X	X
data 03
data 04
data 05		X
data 06
data 07				X
data 08	X
data 09
data 10			X
data 11
data 12		X
data 13
data 14			X
data 15
data 16	X
data 17		X	X	X
data 18
data 19		X
data 20

The formula I’m trying is {=CountA(If(CountA(B:E)>0, A:A))} and it’s an ArrayFormula.
The result should be 11 (the number of shaded rows), however it’s evaluating 20 (the total number of rows).

What am I doing wrong?

Thanks

EDIT:
I know to workaround this problem. For instance, I could count the number of X’s on each row and then count those results. But I want to do this with a single formula, if it’s possible.
Also, I don’t want to use the countifs formula because, in the real problem, the number of columns with X’s is a LOT bigger.

>Solution :

You could try this using MMULT()

=SUM(--(MMULT(N(B1:E20="X"),{1;1;1;1})>0))

To make it more readable could use the following note that this formula works with Excel 2021+ onwards,

=LET(
     _Data, A1:E20,
     _Ncolumns, DROP(_Data,,1),
     _Columns, COLUMNS(_Ncolumns),
     SUM(--(MMULT(N(_Ncolumns="X"),SEQUENCE(_Columns)^0)>0)))

If Column A has to be validated as well then:

=SUM(--(MMULT(N((B1:E20="X")*(A1:A20<>"")),{1;1;1;1})>0))

Or, Dynamic approach:

=LET(
     _Data, A1:E20,
     _ColA, TAKE(_Data,,1),
     _Ncolumns, DROP(_Data,,1),
     _Columns, COLUMNS(_Ncolumns),
     SUM(--(MMULT(N((_Ncolumns="X")*(_ColA<>"")),SEQUENCE(_Columns)^0)>0)))