Distinct count with another if condition

[tomas]

Hi

I know how to make distinct count of values in b column and the formula for this is in d3. I want to expand the formula to make distinct count of those values in b column which have corresponding value in A column "real" so the result should by 4 excluding the highlighted row.

thx in advance.

 

[NARAYANK991]

Hi ,

This is a standard formula :

=SUM(IF($A$3:$A$8 = "real", 1/COUNTIFS($A$3:$A$8, "real", $B$3:$B$8, $B$3:$B$8)))

Enter this as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[rahulshewale1]

hii,

try below formula

=SUMPRODUCT(($A$3:$A$8="real")*(1/COUNTIFS(B3:B8,B3:B8)))


Regard
Rahul shewale

 

[tomas]

thank you

 

[tomas]

gives me 3.5 as a result. I mean Rahulshewale solution

 

[rahulshewale1]

hii,

narayan sir formula work great ,,

revised formula,


=SUMPRODUCT(($A$3:$A$8="real")*IFERROR(1/COUNTIFS($A$3:$A$8,"real",$B$3:$B$8,$B$3:$B$8),0))

Enter this as an array formula , using CTRL SHIFT ENTER.
Regard
rahul shewale