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]

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?

Try this...

Array entered**:

=SUM(IF(FREQUENCY(IF(A2:A10=918,B2:B10),B2:B10),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[mikechambers]

That's awesome! Thanks!!!!!

 

[T. Valko]

That's awesome! Thanks!!!!!

You're welcome!

 

[mikechambers]

So is there any reason this would not work with text fields? I used it for the one instance with the dates and it worked fine. But now I'm trying it in another spreatsheet, where instead of dates I am trying to find the number of unique names and it's coming back 0 everytime. And I did the CSE.

 

[mikechambers]

OK I just went into Help and saw that Frequency ignores Text. So is there another way to do this same thing with text?

 

[T. Valko]

So is there any reason this would not work with text fields? I used it for the one instance with the dates and it worked fine. But now I'm trying it in another spreatsheet, where instead of dates I am trying to find the number of unique names and it's coming back 0 everytime. And I did the CSE.

Counting unique text entries is a bit more complicated.

Book1
	A	B	C
2	918	7/18/2011	text1
3	918	7/18/2011	text1
4	917	7/22/2011	text1
5	915	7/21/2011	text2
6	918	7/19/2011	text3
7	919	7/21/2011	text3
8	916	7/18/2011	text3
9	918	7/20/2011	text3
10	919	7/19/2011	text4
Sheet2

Count the unique text in column C if column A = 918.

Array entered**:

=SUM(IF(FREQUENCY(IF(A2:A10=918,MATCH(C2:C10,C2:C10,0)),ROW(C2:C10)-ROW(C2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the data range of column C.

 

[mikechambers]

Once again, Awesome!! Thank you!!!

One question though. I am copying this down to determine the counts for different numbers (918, 919, 920, etc) so obviously I have a cell reference in place of the 918 that's hard coded. So I also then have all of the ranges in your formula locked both col and row. So what does the ROW(C2) do exactly? Wouldn't that always be a hard 2?

 

[T. Valko]

Once again, Awesome!! Thank you!!!

One question though. I am copying this down to determine the counts for different numbers (918, 919, 920, etc) so obviously I have a cell reference in place of the 918 that's hard coded. So I also then have all of the ranges in your formula locked both col and row. So what does the ROW(C2) do exactly? Wouldn't that always be a hard 2?

MATCH returns an array of numbers from 1 to n.

The frequency bins also have to be an array of numbers that correspond to the array returned by MATCH. We use the ROW function to generate the bins.

If the range is C2:C10, a total of 9 cells, then MATCH will return an array of numbers from 1 to 9. Using the ROW function we have to generate the bins array from 1 to 9. Here's how we do that:

ROW(C2:C10)-ROW(C2)+1

ROW(C2)-ROW(C2)+1 = (2)-(2)+1 = 1
ROW(C3)-ROW(C2)+1 = (3)-(2)+1 = 2
ROW(C4)-ROW(C2)+1 = (4)-(2)+1 = 3
ROW(C5)-ROW(C2)+1 = (5)-(2)+1 = 4
ROW(C6)-ROW(C2)+1 = (6)-(2)+1 = 5
...
...
ROW(C10)-ROW(C2)+1 = (10)-(2)+1 = 9

 

[mikechambers]

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