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>
 

Some videos you may like

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

Watch MrExcel Video

Forum statistics

Threads
1,108,956
Messages
5,525,883
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top