Count in two columns

[j.figueras]

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

 

[Leo Heuser]

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<>""))

 

[T. Valko]

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

Biff

 

[Guest]

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

ctrl+shift+enter

 

[Guest]

Maybe this?:

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

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

XL2002, WinXP

 

[Guest]

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

 

[j.figueras]

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