count unique values with multiple criteria

D

dakobbie03

New Member
Joined
Sep 16, 2014
Messages
4
I can't wrap my head around what I need to do in order to count the number of distinct "Calculated Rates" with a certain "Account Number"? so with the data below, 10008-06785-5-00001</SPAN> would be 3 because there are 3 distinct rates under this account number. so how can i do this with a formula for my whole list?
ACCOUNTNUMBER</SPAN>
CALCULDATED_RATE</SPAN>
10008-06785-5-00001</SPAN>
0.549</SPAN>
10008-06785-5-00001</SPAN>
0.549</SPAN>
10008-06785-5-00001</SPAN>
0.549</SPAN>
10008-06785-5-00001</SPAN>
0.549</SPAN>
10008-06785-5-00001</SPAN>
0.579</SPAN>
10008-06785-5-00001</SPAN>
0.579</SPAN>
10008-06785-5-00001</SPAN>
0.579</SPAN>
10008-06785-5-00001</SPAN>
0.579</SPAN>
10008-06785-5-00001</SPAN>
0.58</SPAN>
10116-26330-7-00001</SPAN>
0.689</SPAN>
10116-26330-7-00001</SPAN>
0.689</SPAN>
10116-26330-7-00001</SPAN>
0.689</SPAN>
10116-26330-7-00001</SPAN>
0.689</SPAN>
10116-26330-7-00001</SPAN>
0.579</SPAN>
10116-26330-7-00001</SPAN>
0.579</SPAN>
10116-26330-7-00001</SPAN>
0.579</SPAN>
10434-68820-4-00001</SPAN>
0.599</SPAN>
10434-68820-4-00001</SPAN>
0.599</SPAN>
10434-68820-4-00001</SPAN>
0.599</SPAN>
10434-68820-4-00001</SPAN>
0.599</SPAN>
10434-68820-4-00001</SPAN>
0.579</SPAN>
10434-68820-4-00001</SPAN>
0.579</SPAN>
10434-68820-4-00001</SPAN>
0.579</SPAN>
10434-68820-4-00001</SPAN>
0.579</SPAN>
10434-68820-4-00001</SPAN>
0.579</SPAN>
10434-68820-4-00001</SPAN>
0.579</SPAN>

<TBODY>
</TBODY>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
maybe...

Excel 2012
ABCD
1ACCOUNTNUMBERCALCULDATED_RATEACCOUNTNUMBER
210008-06785-5-000010.54910008-06785-5-00001
310008-06785-5-000010.5493
410008-06785-5-000010.549
510008-06785-5-000010.549
610008-06785-5-000010.579
710008-06785-5-000010.579
810008-06785-5-000010.579
910008-06785-5-000010.579
1010008-06785-5-000010.58
1110116-26330-7-000010.689
1210116-26330-7-000010.689
1310116-26330-7-000010.689
1410116-26330-7-000010.689
1510116-26330-7-000010.579
1610116-26330-7-000010.579
1710116-26330-7-000010.579
1810434-68820-4-000010.599
1910434-68820-4-000010.599
2010434-68820-4-000010.599
2110434-68820-4-000010.599
2210434-68820-4-000010.579
2310434-68820-4-000010.579
2410434-68820-4-000010.579
2510434-68820-4-000010.579
2610434-68820-4-000010.579
2710434-68820-4-000010.579

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Array Formulas
CellFormula
D3{=SUM(IF(FREQUENCY(IF((B2:B27<>"")*(A2:A27=D2),MATCH(B2:B27,B2:B27,0)),ROW(B2:B27)-ROW(B2)+1),1))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
In your screenshot you would need to lock the row.

See if this works for you


Excel 2012
ABCDE
1ACCOUNTNUMBERCALCULDATED_RATEACCOUNTNUMBERUnique
210008-06785-5-000010.54910008-06785-5-000013
310008-06785-5-000010.54910116-26330-7-000012
410008-06785-5-000010.54910434-68820-4-000012
510008-06785-5-000010.549
610008-06785-5-000010.579
710008-06785-5-000010.579
810008-06785-5-000010.579
910008-06785-5-000010.579
1010008-06785-5-000010.58
1110116-26330-7-000010.689
1210116-26330-7-000010.689
1310116-26330-7-000010.689
1410116-26330-7-000010.689
1510116-26330-7-000010.579
1610116-26330-7-000010.579
1710116-26330-7-000010.579
1810434-68820-4-000010.599
1910434-68820-4-000010.599
2010434-68820-4-000010.599
2110434-68820-4-000010.599
2210434-68820-4-000010.579
2310434-68820-4-000010.579
2410434-68820-4-000010.579
2510434-68820-4-000010.579
2610434-68820-4-000010.579
2710434-68820-4-000010.579

<tbody>
</tbody>
Sheet1​

Array Formulas
CellFormula
E2{=SUM(IF(FREQUENCY(IF(($B$2:$B$27<>"")*($A$2:$A$27=D2),MATCH($B$2:$B$27,$B$2:$B$27,0)),ROW($B$2:$B$27)-ROW($B$2)+1),1))}
E3{=SUM(IF(FREQUENCY(IF(($B$2:$B$27<>"")*($A$2:$A$27=D3),MATCH($B$2:$B$27,$B$2:$B$27,0)),ROW($B$2:$B$27)-ROW($B$2)+1),1))}
E4{=SUM(IF(FREQUENCY(IF(($B$2:$B$27<>"")*($A$2:$A$27=D4),MATCH($B$2:$B$27,$B$2:$B$27,0)),ROW($B$2:$B$27)-ROW($B$2)+1),1))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Last edited:
Upvote 0
sorry, had to make a change, see if the formulas in the previous post are working for you now
 
Upvote 0
Is there a difference with excel 2012 and 2010 for the reason i'm getting my result as shown below. I have everything entered the same as you do above.

10008-06785-5-000011
10116-26330-7-00001#VALUE!
10434-68820-4-00001#VALUE!

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
make sure you are entering the formula with Control Shift Enter in windows or Command Enter on a Mac
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
172
zero269
Z
M
  • Solved
Counting unique values by date condition and another criteria
Replies
5
Views
2K
liaragon
L
L
  • Solved
Sum unique values based on multiple criteria
Replies
8
Views
319
liulilli
L
K
  • Solved
Count Unique Values Based On Criteria
Replies
7
Views
297
Fluff
Fluff
G
  • Question
Sum Unique Value based on different Time criterias
Replies
5
Views
946
sandy666
sandy666

Forum statistics

Threads
1,214,568
Messages
6,120,278
Members
448,953
Latest member
Dutchie_1

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