Count Non-blank Rows

[natkoy]

Again I've Googled hours looking for a solution to this (including searching this forum) :-(

I have the following table and would like to get the number of rows where EITHER column A OR B is NOT blank (has either text or number).

column A	column B	column C
50		100
	Has Text	100
		90
50	Has Text	100

So the result in this case would be 3.

Thanks for your help!

[LeoTaxi]

why not 4 ?

[José Augusto]

Try this array formula
=SUM(--((--(A1:A4<>"")+(--(B1:B4<>"")))>0))

Please Login or Register  to view this content.

[cbatrody]

Hi,

Try the following formula:

=ROWS(A2:A5)-COUNTIFS(A2:A5,"=",B2:B5,"=")

[Shareez Saleem]

Try this formula:

2-COUNTA(A1:B1)
If the value is showing 1, one of the cells is blank
If the value is showing 2, both cells are blank
If the value is showing 0, both cells are not empty

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	50		100		3
2		Has Text	100
3			90
4	50	Has Text	100
5	---------	---------	---------	---------	---------

This formula entered in E1:

=SUMPRODUCT(SIGN((A1:A5<>"")+(B1:B5<>"")))

[natkoy]

Thank you José — that worked nicely.

I also liked (and wound up using) Tony Valko's option because it doesn't need to be entered as an ARRAY FORMULA.

[Tony Valko]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[chief_abound]

Hi cbatrody

if I may ask...

??? 3.jpg

[cbatrody]

Hi chief,

COUNTIFS(A2:A5,"=",B2:B5,"=")

"=" returns count of rows that are blank in column A & column B within the mentioned range (rows 2:5)

[chief_abound]

Thanks for your help!

[Tony Valko]

COUNTIFS(A2:A5,"=",B2:B5,"=")

"=" returns count of rows that are blank in column A & column B within the mentioned range (rows 2:5)

This will do the same thing:

COUNTIFS(A2:A5,"",B2:B5,"")