count unique values with multiple criteria

[dakobbie03]

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?
[TABLE="width: 365"]
<TBODY>[TR]
[TD]ACCOUNTNUMBER</SPAN>
[/TD]
[TD]CALCULDATED_RATE</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.549</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.549</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.549</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.549</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10008-06785-5-00001</SPAN>
[/TD]
[TD]0.58</SPAN>
[/TD]
[/TR]
[TR]
[TD]10116-26330-7-00001</SPAN>
[/TD]
[TD]0.689</SPAN>
[/TD]
[/TR]
[TR]
[TD]10116-26330-7-00001</SPAN>
[/TD]
[TD]0.689</SPAN>
[/TD]
[/TR]
[TR]
[TD]10116-26330-7-00001</SPAN>
[/TD]
[TD]0.689</SPAN>
[/TD]
[/TR]
[TR]
[TD]10116-26330-7-00001</SPAN>
[/TD]
[TD]0.689</SPAN>
[/TD]
[/TR]
[TR]
[TD]10116-26330-7-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10116-26330-7-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10116-26330-7-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.599</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.599</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.599</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.599</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001</SPAN>
[/TD]
[TD]0.579</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Weazel]

maybe...

Excel 2012

	A	B	C	D
10008-06785-5-00001	10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]ACCOUNTNUMBER[/TD]
[TD="bgcolor: #FFFF00"]CALCULDATED_RATE[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]ACCOUNTNUMBER[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.549[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.549[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0.549[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0.549[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]0.58[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]0.689[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0.689[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0.689[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]0.689[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]0.599[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]0.599[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]0.599[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]0.599[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF((B2:B27<>"")*(A2:A27=D2),MATCH(B2:B27,B2:B27,0)),ROW(B2:B27)-ROW(B2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[dakobbie03]

Thanks for your reply. This is a great start, but my answer is coming out different. see the screenshot link. I get "1" for the first account number and #value for the second.

https://plus.google.com/photos/106484111943055717152/albums/6059672556779752129?authkey=CIDo1cvOrMbztQE

 

[Weazel]

In your screenshot you would need to lock the row.

See if this works for you


Excel 2012

	A	B	C	D	E
10008-06785-5-00001	10008-06785-5-00001
10008-06785-5-00001	10116-26330-7-00001
10008-06785-5-00001	10434-68820-4-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10008-06785-5-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10116-26330-7-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001
10434-68820-4-00001

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]ACCOUNTNUMBER[/TD]
[TD="bgcolor: #FFFF00"]CALCULDATED_RATE[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]ACCOUNTNUMBER[/TD]
[TD="bgcolor: #FFFF00"]Unique[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.549[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.549[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0.549[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0.549[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]0.58[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]0.689[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0.689[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0.689[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]0.689[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]0.599[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]0.599[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]0.599[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]0.599[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]0.579[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

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

 

[dakobbie03]

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.

[TABLE="width: 407"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]10008-06785-5-00001[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10116-26330-7-00001[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]10434-68820-4-00001[/TD]
[TD]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

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

 

[dakobbie03]

Thank you!

 

[Weazel]

you're welcome, glad you got it working