Counting Rows Once In Range Which Contain Data

Question

I am trying to figure out a formula that will count each row in a range, that contains any cell that is not empty. For example

      Item 1|Item 2|Item 3
Cat 1   X      X      X
Cat 2
Cat 3   X      X      X

And

      Item 1|Item 2|Item 3
Cat 1   X              
Cat 2
Cat 3                 X

Should both return 2. I know I could use a helper column with COUNTIF(Row,"<>") then use COUNTIF(Column,">0"), but I'm trying to avoid doing that because it will create other issues. Is there a way to do what I want all in one formula?

user_9758194 · Answer

Depending on the version of Excel:

=SUMPRODUCT(--((B2:B4&C2:C4&D2:D4)<>""))

Swap SUMPRODUCT() for SUM() if on ms365.

user_18824727 · Answer

An obviously much longer formula but, for larger data ranges, less time-consuming to construct, as it doesn't involve selecting each column individually:

=ROWS(B2:P6)-SUM(INT(MMULT(--ISBLANK(B2:P6),TRANSPOSE(COLUMN(B2:P6)/COLUMN(B2:P6)))/COLUMNS(B2:P6)))

(as an array formula, this runs in Excel 2007 and later, although it could be shortened a lot in Office 365)

Counting Rows Once In Range Which Contain Data

2 Answers