Counting unique values with multiple criteria

O

omarman

New Member
Joined
Feb 25, 2009
Messages
35
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>
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Welcome to the Board,

Try this equation to count:
Code: 
=SUMPRODUCT(--($D$1:$D$13=2366),--($C$1:$C$13=2005),--($A$1:$A$13="T"))
Good Luck. - Bob
 
Upvote 0
bobinnc said:
Try this equation to count:
Code: 
=SUMPRODUCT(--($D$1:$D$13=2366),--($C$1:$C$13=2005),--($A$1:$A$13="T"))
Good Luck. - Bob
Click to expand...
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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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*
 
Upvote 0
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
 
Last edited:
Upvote 0
Try...

Code: 
=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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
C
  • Solved
Need to Return Unique Players for Same Multiple Number Result (XLOOKUP?)
2
Replies
14
Views
1K
Fluff
Fluff
M
  • Solved
Counting multiple Criteria within a table
Replies
9
Views
862
Manexcel
M
M
  • Question Question
Drop down select
Replies
1
Views
1K
madu
M
D
  • Question Question
QRCode Generator
Replies
1
Views
2K
drag1c
D

Forum statistics

Threads
1,224,811
Messages
6,181,082
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top