Counting unique values with multiple criteria

[omarman]

Hi, I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:

2366 2005 T [number of corresponding unique values of B]

For example:

--D---C---A-B
2366 2005 T 2
(as opposed to 3)

Any advice would be greatly appreciated!



<table style="border-collapse: collapse;" width="215" border="0" cellpadding="0" cellspacing="0" height="564"><col style="width: 30pt;" width="40"> <col style="width: 47pt;" width="62"> <col style="width: 27pt;" width="36"> <col style="width: 47pt;" width="62"> <tbody><tr style="height: 63.75pt;" height="85"> <td class="xl65" style="height: 63.75pt; width: 30pt;" width="40" height="85">A</td> <td valign="top">
</td><td class="xl65" style="width: 47pt;" width="62"> B
</td> <td class="xl65" style="width: 27pt;" width="36"> C</td> <td class="xl65" style="width: 47pt;" width="62"> D
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">T</td> <td valign="top">
</td><td>655</td> <td align="right">2005</td> <td align="right">2366</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">T</td> <td valign="top">
</td><td>656</td> <td align="right">2005</td> <td align="right">2366</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">T</td> <td valign="top">
</td><td>656</td> <td align="right">2005</td> <td align="right">2366</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">W</td> <td valign="top">
</td><td>659</td> <td align="right">2005</td> <td align="right">2367</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">W</td> <td valign="top">
</td><td>659</td> <td align="right">2008</td> <td align="right">2369</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">W</td> <td valign="top">
</td><td>659</td> <td align="right">2006</td> <td align="right">2370</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">F</td> <td valign="top">
</td><td>659</td> <td align="right">2005</td> <td align="right">2370</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">W</td> <td valign="top">
</td><td>660</td> <td align="right">2005</td> <td align="right">2370</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">W</td> <td valign="top">
</td><td>660</td> <td align="right">2008</td> <td align="right">2371</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">W</td> <td valign="top">
</td><td>660</td> <td align="right">2006</td> <td align="right">2371</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">T</td> <td valign="top">
</td><td>660</td> <td align="right">2005</td> <td align="right">2371</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">W</td> <td valign="top">
</td><td>661</td> <td align="right">2005</td> <td align="right">2372</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">W</td> <td valign="top">
</td><td>661</td> <td align="right">2007</td> <td align="right">2372</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td valign="top">
</td><td>
</td> <td align="right">
</td> <td align="right">
</td> </tr> </tbody></table>

 

[bobinnc]

Welcome to the Board,

Try this equation to count:

=SUMPRODUCT(--($D$1:$D$13=2366),--($C$1:$C$13=2005),--($A$1:$A$13="T"))

Good Luck. - Bob

 

[jbeaucaire]

Try this equation to count:

=SUMPRODUCT(--($D$1:$D$13=2366),--($C$1:$C$13=2005),--($A$1:$A$13="T"))

Good Luck. - Bob

That gives a result of 3, the OP already indicated the desired answer is 2, the number of "different" values in column B where all the other 3 columns match.

 

[kieran]

omarman,

In another column, concatenat the values of column A, B c & D ( =A2&B2&C2&D2 or =Concatenate(A2,B2,C2,D2) adn copy down.

Then use =COUNTIF on the whole list - eg =COUNTIF(TheConatentated Column, TheFirstValueInConacatenatedColum) and copy down.

Those entries with a countif value of 1 are the unique ones. (use auto filter to find them fast)

 

[mikerickson]

Perhaps,
=SUMPRODUCT(--(A1:A13="T"), --(C1:C13=2005), --(D1:D13=2366), 1/COUNTIF(B1:B13,B1:B13))

 

[Sal Paradise]

This gives the unique array:
=SUM(IF((A1:A13=H1)*(C1:C13=G1)*(D1:D13=F1)=1,B1:B13))

But if I try to combine that with the function for unique values (SUM(1/COUNTIF(Range,Range)) it gives me a #VALUE! error for some reason. Maybe it can't be done with an array function?

 

[Sal Paradise]

Perhaps,
=SUMPRODUCT(--(A1:A13="T"), --(C1:C13=2005), --(D1:D13=2366), 1/COUNTIF(B1:B13,B1:B13))

And here I was fumbling with an array formula when SUMPRODUCT can handle this too!

 

[jbeaucaire]

That sumproduct worked for the first values at the top of the chart, but used to test the other three cell pairs, it gives .5 and 0.25 answers when the answers should be 1. *puzzled*

 

[omarman]

Thanks so much, Everyone!

I tried Kieran's advice and used the =CONCATENATE(A2,B2,C2,D2) and copied and pasted down on a new column, then used =COUNTIF(concatenatedcolumn, firstcellofcontatenatedcolumn). Then I autofiltered with criteria of 1 from the COUNTIF column. This worked great.

I also tried to using the SUMPRODUCT but receive a #VALUE! error. I'm going to keep trying. The actual data set I'm working with is large and this needs to be done a few times so one function and explanation would be nice.

Thanks for help, I'm new to this site and love it already!

omarman

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF($A$2:$A$14="T",IF($C$2:$C$14=2005,IF($D$2:$D$14=2366,IF($B$2:$B$14<>"",MATCH("~"&$B$2:$B$14,$B$2:$B$14&"",0))))),ROW($B$2:$B$14)-ROW($B$2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!