I want to count non-blank cells in two columns, but count only one of
them if both are non-blank.
The following example would yield 4
4 t
3
5 y
u

skrev i en meddelelse
oups.com...
I want to count non-blank cells in two columns, but count only one of
them if both are non-blank.
The following example would yield 4
4 t
3
5 y
u


One way:

=COUNTA(A2:B20)-SUMPRODUCT((A1:A20<"")*(B1:B20<""))


--
Best regards
Leo Heuser

Followup to newsgroup only please.

=SUMPRODUCT(--((A1:A10<"")+(B1:B10<"")0))

Biff

wrote in message
oups.com...
I want to count non-blank cells in two columns, but count only one of
them if both are non-blank.
The following example would yield 4
4 t
3
5 y
u

=SUM(IF((A4:A7<"")+(B4:B7<""),1))

ctrl+shift+enter

" wrote:

I want to count non-blank cells in two columns, but count only one of
them if both are non-blank.
The following example would yield 4
4 t
3
5 y
u

Maybe this?:

=SUMPRODUCT(--(LEN(A4:A7&B4:B7)0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

I want to count non-blank cells in two columns, but count only one of
them if both are non-blank.
The following example would yield 4
4 t
3
5 y
u

hi J.F

if u are aware of using an array formula by pressing ctrl-shft-enter
then you may also consider the sum array function...

=SUM(--((A1:A10<"")+(B1:B10<"")0))

regards
--
*****
birds of the same feather flock together..



" wrote:

I want to count non-blank cells in two columns, but count only one of
them if both are non-blank.
The following example would yield 4
4 t
3
5 y
u

I've seen several references to 'ctrl-shft-enter' in these postings,
but I don't know about it. Thanks to all who responded! JF

On Feb 3, 9:18 am, driller wrote:
hi J.F

if u are aware of using an array formula by pressing ctrl-shft-enter
then you may also consider the sum array function...

=SUM(--((A1:A10<"")+(B1:B10<"")0))

regards
--
*****
birds of the same feather flock together..

" wrote:
I want to count non-blank cells in two columns, but count only one of
them if both are non-blank.
The following example would yield 4
4 t
3
5 y
u