# count unique values with multiple criteria

#### dakobbie03

##### New Member
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 CALCULDATED_RATE 10008-06785-5-00001 0.549 10008-06785-5-00001 0.549 10008-06785-5-00001 0.549 10008-06785-5-00001 0.549 10008-06785-5-00001 0.579 10008-06785-5-00001 0.579 10008-06785-5-00001 0.579 10008-06785-5-00001 0.579 10008-06785-5-00001 0.58 10116-26330-7-00001 0.689 10116-26330-7-00001 0.689 10116-26330-7-00001 0.689 10116-26330-7-00001 0.689 10116-26330-7-00001 0.579 10116-26330-7-00001 0.579 10116-26330-7-00001 0.579 10434-68820-4-00001 0.599 10434-68820-4-00001 0.599 10434-68820-4-00001 0.599 10434-68820-4-00001 0.599 10434-68820-4-00001 0.579 10434-68820-4-00001 0.579 10434-68820-4-00001 0.579 10434-68820-4-00001 0.579 10434-68820-4-00001 0.579 10434-68820-4-00001 0.579

<TBODY>
</TBODY>

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

</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))}

</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>

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:
sorry, had to make a change, see if the formulas in the previous post are working for you now

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-00001 1 10116-26330-7-00001 #VALUE! 10434-68820-4-00001 #VALUE!

<colgroup><col><col></colgroup><tbody>
</tbody>

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

Thank you!

you're welcome, glad you got it working

Replies
5
Views
1K
Replies
8
Views
276
Replies
7
Views
192
Replies
5
Views
854
Replies
4
Views
807

1,203,762
Messages
6,057,218
Members
444,914
Latest member
Mamun12345

### 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.

### Which adblocker are you using?

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

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