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

<colgroup><col style="width:48pt" width="64" span="3"> </colgroup><tbody>
[TD="class: xl65, width: 64"]ID[/TD]
[TD="class: xl65, width: 64"]Col L[/TD]
[TD="class: xl65, width: 64"]Col M[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Y[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]U[/TD]
[TD="class: xl65"]Y[/TD]

[TD="class: xl65"]7[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Y[/TD]

[TD="class: xl65"]8[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Y[/TD]

[TD="class: xl65"]9[/TD]
[TD="class: xl65"]U[/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl65"]10[/TD]
[TD="class: xl65"]U[/TD]
[TD="class: xl65"]Y[/TD]

[TD="class: xl65"]11[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Y[/TD]

[TD="class: xl65"]12[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Y[/TD]

</tbody>

<tbody>
[TD="class: xl64"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

</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.