Count of unique values with multiple criteria

[mikechambers]

I am trying to get a unique count of dates in one column if another column matches a number. So....

918 7/18/11
918 7/18/11
917 7/22/11
915 7/21/11
918 7/19/11
919 7/21/11
916 7/18/11
918 7/20/11
919 7/19/11

So for example, Column B shows 5 different dates all together. But I want to count how many different dates (unique values) there are if Column A has 918, which should result in 3. Does that make sense? I was playing around with the Frequency function, and had so far:

=SUM(IF(FREQUENCY(B1:B9,B1:B9)>0,1))

This gives me 5, which is the number of unique entries in Column B. Any ideas how I could tweak this?

 

[T. Valko]

So are you saying I should not lock that last C2? I did, and it still seems to work.

If you're drag copying the formula down a column then you want to make the range references row absolute:

=SUM(IF(FREQUENCY(IF(A$2:A$10=918,MATCH(C$2:C$10,C$2:C$10,0)),ROW(C$2:C$10)-ROW(C$2)+1),1))

 

[mikechambers]

I guess that's why I'm asking then why use ROW(C$2) if it's always going to be equal to 2. Don't get me wrong, I'm not questioning you, just trying to understand. I'm not that good with array formulas, so I thought maybe there was something I was missing.

 

[T. Valko]

I guess that's why I'm asking then why use ROW(C$2) if it's always going to be equal to 2. Don't get me wrong, I'm not questioning you, just trying to understand. I'm not that good with array formulas, so I thought maybe there was something I was missing.

The reason we (I) use ROW(C$2) is because I don't know if you might insert new rows before your data range. If you did then all the references in the formula will adjust accordingly and no harm will be done.

However, if you replace ROW(C$2) with the hardcoded value 2 and then you were to insert new rows before the data range then that will "break" the formula.

Try this little test. This is what would happen in the formula.

Enter this formula in C2:

=ROW(C$2)-ROW(C$2)+1

Enter this formula in D2:

=ROW(C$2)-2+1

The formula in D2 is basically what you want to do. You want to replace ROW(C$2) with the hardcoded 2.

Now insert a new row 1 and see what happens to the formula results. For the main formula to work properly these values must not change when rows are inserted.

If you know for certain that you will NEVER insert new rows before the data range then you can replace ROW(C$2) with the hardcoded value 2.

 

[mikechambers]

That makes sense. Just a smarter way of writing the fomula. Didn't think about that aspect. Thanks for the explanation and help!!!

 

[T. Valko]

That makes sense. Just a smarter way of writing the fomula. Didn't think about that aspect. Thanks for the explanation and help!!!

You're welcome. Thanks for the feedback!

 

[bebo021999]

Following Mikechamber's list
918 7/18/11
918 7/18/11
917 7/22/11
915 7/21/11
918 7/19/11
919 7/21/11
916 7/18/11
918 7/20/11
919 7/19/11

I 'd like to make unique list with A-Z order as follow:
<TABLE style="WIDTH: 71pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=94><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 width=94>915 7/21/11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>916 7/18/11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>917 7/22/11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>918 7/18/11

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>918 7/19/11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>918 7/20/11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>919 7/19/11</TD></TR>



<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17>919 7/21/11</TD></TR>
</TBODY></TABLE>

Could you please help me ?