# 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

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

#### Weazel

##### Well-known Member
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>

#### Weazel

##### Well-known Member
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

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

#### dakobbie03

##### New Member
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>

#### Weazel

##### Well-known Member

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

Thank you!

#### Weazel

##### Well-known Member
you're welcome, glad you got it working

Replies
5
Views
308
Replies
4
Views
335
Replies
2
Views
211
Replies
0
Views
96
Replies
7
Views
524