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:
Sort by date Sort by votes
Thanks all. Domenic, I get a NA error with that function. I may be doing something wrong.

=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))

Here is a better example of what I'm working with if anyone would like to take a stab.
A B C D
F 1655 2005 505
F 1656 2005 505
F 1656 2005 505
F 1659 2005 505
F 1659 2005 505
F 1659 2005 505
F 1659 2005 505
F 1660 2006 505
F 1660 2006 505
F 1660 2006 505
F 1660 2006 505
T 437 2006 918
T 437 2006 918
T 437 2006 918
T 461 2007 918
T 461 2007 918
T 461 2007 918
T 461 2007 918
T 464 2007 918
T 464 2007 918
T 464 2007 918
T 466 2007 918
T 466 2007 918
T 466 2007 918
T 469 2007 918
T 469 2007 918
T 469 2007 918

D A C Number of unique occurances of B
505 F 2005 3
505 F 2006 1
918 T 2006 ?
918 T 2007 ?


Thanks!
 
Last edited:
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?
 
Upvote 0
mikerickson said:
Perhaps,
=SUMPRODUCT(--(A1:A13="T"), --(C1:C13=2005), --(D1:D13=2366), 1/COUNTIF(B1:B13,B1:B13))
Click to expand...

When I use Mike's formula in the following manner, the result is 130.5.

=SUMPRODUCT(--(INDEX(Consolidated,0,16)=G25),--(LEFT(INDEX(Consolidated,0,15),2)="PR"),--(MONTH(INDEX(Consolidated,0,24))=MONTH($B$1)),--(INDEX(Consolidated,0,4)="C"),--(MONTH(INDEX(Consolidated,0,14))=MONTH($B$1)),1/COUNTIF(INDEX(Consolidated,0,1),INDEX(Consolidated,0,1)))

Can someone explain this please.
 
Upvote 0
I used an almost identical formula I found from an ExcelIsFun YouTube video:
Code: 
=SUM(IF(FREQUENCY(IF($A$2:$A$12068=A8,MATCH("~"&$CK$2:$CK$12068,$CK$2:$CK$12068&"",0)),ROW($CK$2:$CK$12068)-ROW($CK$2)+1),1))

My question is whether it would be faster to calculate by using VBA? There are about 12,000 rows and even with an i7 quad-core processor on my new laptop, it looks like it would take about an hour to calculate.

Thanks,

Rob.



Domenic said:
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!
Click to expand...
 
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,813
Messages
6,181,117
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