CODEWITHSUNDEEP
excelmultidimensional-arrayrowformulacountif
Aaron Anderson
Aaron Anderson

Reputation: 1

excel how do I count the number of rows containing data?

In Excel, I am attempting to count the number of rows that contain data in a 31 by 17 table. The following formula does the job, but it is cumbersome and not dynamic. Is there a way to improve this formula using a function or array formula?

=SUM(IF(SUM(B3:AF3)>0,1),IF(SUM(B4:AF4)>0,1),IF(SUM(B5:AF5)>0,1),IF(SUM(B6:AF6)>0,1),IF(SUM(B7:AF7)>0,1),IF(SUM(B8:AF8)>0,1),IF(SUM(B9:AF9)>0,1),IF(SUM(B10:AF10)>0,1),IF(SUM(B11:AF11)>0,1),IF(SUM(B12:AF12)>0,1),IF(SUM(B13:AF13)>0,1),IF(SUM(B14:AF14)>0,1),IF(SUM(B15:AF15)>0,1),IF(SUM(B16:AF16)>0,1),IF(SUM(B17:AF17)>0,1),IF(SUM(B18:AF18)>0,1),IF(SUM(B19:AF19)>0,1))

Possible answers that i haven't gotten to work have been DBCOUNTA() and SUMPRODUCT(--(SUMIFS)).

Upvotes: 0

Views: 4187

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

If you have only 17 rows, then try:

=(COUNTA(1:1)>0)+(COUNTA(2:2)>0)+(COUNTA(3:3)>0)+(COUNTA(4:4)>0)+(COUNTA(5:5)>0)+(COUNTA(6:6)>0)+(COUNTA(7:7)>0)+(COUNTA(8:8)>0)+(COUNTA(9:9)>0)+(COUNTA(10:10)>0)+(COUNTA(11:11)>0)+(COUNTA(12:12)>0)+(COUNTA(13:13)>0)+(COUNTA(14:14)>0)+(COUNTA(15:15)>0)+(COUNTA(16:16)>0)+(COUNTA(17:17)>0)

Upvotes: 0

XOR LX
XOR LX

Reputation: 7742

There are three general approaches to this problem.

The first - and arguably most sensible (and certainly easiest to understand) - option is to make use of an additional column within your existing table. For example, assuming we use column AG for this purpose, we would enter this formula in AG3:

=COUNT(B3:AF3)>0

and copy down as required.

After which your count is obtained via simply:

=COUNTIF(AG3:AG19,TRUE)

If, for whatever reason, you are unable (or indeed unwilling) to use an additional column, then there are two principal choices.

The first of these is perhaps more intuitively comprehensible, though suffers from the fact that it is volatile, viz:

=SUMPRODUCT(0+(SUBTOTAL(2,OFFSET(B3:AF3,ROW(B3:AF19)-MIN(ROW(B3:AF19)),))>0))

The alternative, non-volatile (and therefore preferable, in my opinion) construction, is the following array formula**:

=SUM(0+(MMULT(0+(LEN(B3:AF19)>0),TRANSPOSE(COLUMN(B3:AF19)^0))>0))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Upvotes: 1

Related Questions