count unique values with multiple criteria

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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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>
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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:

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155

ADVERTISEMENT

sorry, had to make a change, see if the formulas in the previous post are working for you now
 

dakobbie03

New Member
Joined
Sep 16, 2014
Messages
4
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>
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155

ADVERTISEMENT

make sure you are entering the formula with Control Shift Enter in windows or Command Enter on a Mac
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,982
Members
430,100
Latest member
namhnz

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
Top