COUNTA/array

[Remster]

Hi Folks

Please see the image below, which is a simplification of my spreadsheet (there are in fact a few more columns and many more rows). I'm trying to come up with a single formula in E6 that counts the number of rows that contain at least one non-blank cell, so in the current example the value in E6 would be 3. I'm sure this has something to do with COUNTA and arrays, but I can't get my head round it.

Thanks

Remster

 

[Remster]

Oops, no image. Isn't it possible to add attachments on this site?

Anyway, the table looks something like this:

__A__________B__________C__________D__________E__________
1____________Apples______Oranges____Bananas_______________
2_Tom________X___________________________________________
3_****___________________X________________________________
4_Harry_______X_____________________X_____________________
5_Dave___________________________________________________
6_____________________________________________ ?__________

 

[number kruncher]

A simple way to achieve this, would be to count the blanks in col E, eg.

=COUNTBLANK(B2:D2)

Then count all the resulting values <3

=COUNTIF(E2:E5,"<3")

 

[Remster]

Yep, I'd though of that, thanks. I just wondered whether it could be done without making the intermediate calculations.

 

[T. Valko]

Yep, I'd though of that, thanks. I just wondered whether it could be done without making the intermediate calculations.

What version of Excel are you using?

What is the EXACT range of cells involved?

 

[Aladin Akyurek]

Oops, no image. Isn't it possible to add attachments on this site?

Anyway, the table looks something like this:

__A__________B__________C__________D__________E__________
1____________Apples______Oranges____Bananas_______________
2_Tom________X___________________________________________
3_****___________________X________________________________
4_Harry_______X_____________________X_____________________
5_Dave___________________________________________________
6_____________________________________________ ?__________

E6, control+shift+enter, not just enter:

=SUM(IF(MMULT((B2:D5="X")+0,TRANSPOSE(COLUMN(B2:D5)^0))>0,1))

 

[T. Valko]

What version of Excel are you using?

What is the EXACT range of cells involved?

Try one of these...

Book1
	A	B	C	D	E
1		Stuff1	Stuff2	Stuff3	Stuff4
2	Name1	X	_	X	X
3	Name2	_	X	_	_
4	Name3	_	_	_	X
5	Name4	_	_	_	_
6	Name5	X	_	_	_
Sheet3

If there are only a "few" columns involved...

=SUMPRODUCT(--(MMULT(--(B2:E6="x"),{1;1;1;1})>0))

In that formula the array constant {1;1;1;1} represents the number of columns in the range. There are 4 columns in B2:E6 so there are 4 1s in the array.

If there are "many" columns then you wouldn't necessarily want to do this:

{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;}

If there are "many" columns then use this array entered** version:

=SUM(--(MMULT(--(B2:E6="x"),TRANSPOSE(COLUMN(B2:E6))^0)>0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

TRANSPOSE(COLUMN(B2:E6))^0 is "expensive" so if you can reasonably avoid it, do so.

 

[pgc01]

Hi

Another option:

=SUM(0+(FREQUENCY(IF(B2:E6="x",ROW(B2:E6)),ROW(B2:E6))>0))

... confirmed with CSE

 

[Remster]

Thanks, all. The 'Transpose' option works perfectly well for my purposes (yes, I have a lot of rows). If I find time, I'll try to work out why it works. I'll also try the last suggestion, out of interest.

 

[T. Valko]

Thanks, all. The 'Transpose' option works perfectly well for my purposes (yes, I have a lot of rows). If I find time, I'll try to work out why it works. I'll also try the last suggestion, out of interest.

You're welcome. Thanks for the feedback!