CountA multiple columns no duplicate counts

[HuskyJones]

Hey Guru's!!
I've done a counta =COUNTA(L4:M10000) which gives me every cell with data.
but .......
I also need unique's in that if L4 and M4 both have data it's only counted once?
I know it's going to be something simple that i'll smack my forehead in shame for after but you guys always clear my excel fog

Cheers

 

[etaf]

=sumproduct(($l$4:$m$10000<>"")/countif($l$4:$m$10000,$l$4:$m$10000&""))

 

[HuskyJones]

Sorry, been away
Nope that didn't work
I have 75 in col L, 121 in col M. my total contacts are 196 which is correct.

There are 29 duplicates with data in both columns that I need only recognising once - so 167 uniques
Could I count all in col L, but then say only count col M if Col L is blank??

arrgghhh brain fart!

 

[Aladin Akyurek]

Try to post a small sample from L4:M10000 (say 10 rows) along with the result which must obtain for that sample.

 

[HuskyJones]

Try to post a small sample from L4:M10000 (say 10 rows) along with the result which must obtain for that sample.

Thanks Aladin

Similar to this......
Total will be 12
Uniques will be 10

ID	Col L	Col M
1	Y
2	Y
3		Y
4
5
6	U	Y
7		Y
8		Y
9	U
10	U	Y
11		Y
12		Y

<colgroup><col style="width:48pt" width="64" span="3"> </colgroup><tbody>
</tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF($L$2:$L$13&$M$2:$M$13="",0,1))

This yields a count of 10.

Just enter:

=COUNTIFS($L$2:$M$13,"?*")

This yields a count of 12.

Is this the processing you have in mind?

 

[HuskyJones]

Control+shift+enter, not just enter:

=SUM(IF($L$2:$L$13&$M$2:$M$13="",0,1))

This yields a count of 10.

Just enter:

=COUNTIFS($L$2:$M$13,"?*")

This yields a count of 12.

Is this the processing you have in mind?

You are an ANGEL!!

 

[Aladin Akyurek]

You are an ANGEL!!

You are welcome.