COUNT distinct values (Dates), with creteria that catches another column

[levynger]

Hi, Im trying to figure a way to count ColumnB (dates) or unique values only for single name in column A (names)
so the result for would by

name1 = 2
name2 = 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]name1[/TD]
[TD]1/1/2000
[/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD]2/1/2000[/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD]2/1/2000[/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD]1/1/2000[/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD]2/1/2000[/TD]
[/TR]
</tbody>[/TABLE]

any help is much appreciated..

 

[tyija1995]

Hey,

I have wrote a formula that will do this given that the names are grouped together... so:
name1
name1
name1
name2
name2
name3
name3
name4
name4
... and so forth (very important that they are grouped!)

Then in column D (D1:D2) in the example provided (assuming that the name3 you put was supposed to be name2) (?) put a list of unique names
i.e. D1 = name1 and D2 = name2
Then in cell E1 try this:

SUM(IF(FREQUENCY(INDIRECT(ADDRESS(ROW(OFFSET($B$1,MATCH(D1,$A$1:$A$5,FALSE)-1,0)),2)):INDIRECT(ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(ROW(OFFSET($B$1,MATCH(D1,$A$1:$A$5,FALSE)-1,0)),2)),COUNTIF($A$1:$A$5,D1)-1,0)),2)),INDIRECT(ADDRESS(ROW(OFFSET($B$1,MATCH(D1,$A$1:$A$5,FALSE)-1,0)),2)):INDIRECT(ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(ROW(OFFSET($B$1,MATCH(D1,$A$1:$A$5,FALSE)-1,0)),2)),COUNTIF($A$1:$A$5,D1)-1,0)),2))),1))

for cell E1 it will return 2 and E2 will return 2.

Change the ranges to see fit to your situation and remember to sort the names so they are grouped then it will work

EDIT: Made the range in column A dynamic so formula should work without changing the range, (Assuming you have ONLY the names in column A - no blank rows between names either!)

SUM(IF(FREQUENCY(INDIRECT(ADDRESS(ROW(OFFSET($B$1,MATCH(D1,$A$1:INDIRECT(ADDRESS(COUNTA(A:A),1)),FALSE)-1,0)),2)):INDIRECT(ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(ROW(OFFSET($B$1,MATCH(D1,$A$1:INDIRECT(ADDRESS(COUNTA(A:A),1)),FALSE)-1,0)),2)),COUNTIF($A$1:INDIRECT(ADDRESS(COUNTA(A:A),1)),D1)-1,0)),2)),INDIRECT(ADDRESS(ROW(OFFSET($B$1,MATCH(D1,$A$1:INDIRECT(ADDRESS(COUNTA(A:A),1)),FALSE)-1,0)),2)):INDIRECT(ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(ROW(OFFSET($B$1,MATCH(D1,$A$1:INDIRECT(ADDRESS(COUNTA(A:A),1)),FALSE)-1,0)),2)),COUNTIF($A$1:INDIRECT(ADDRESS(COUNTA(A:A),1)),D1)-1,0)),2))),1))

 

[tyija1995]

Apologies it's a bit messy with the solution above, please see this example in practice (including headers so I have adapted the formula to start from the 2nd row instead of the first:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Unique Names[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dave[/TD]
[TD]05/02/2000[/TD]
[TD][/TD]
[TD]Dave[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Edward[/TD]
[TD]05/03/2000[/TD]
[TD][/TD]
[TD]Edward[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Edward[/TD]
[TD]06/03/2000[/TD]
[TD][/TD]
[TD]Sally[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Edward[/TD]
[TD]09/03/2000[/TD]
[TD][/TD]
[TD]Ian[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sally[/TD]
[TD]05/03/2000[/TD]
[TD][/TD]
[TD]Emma[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sally[/TD]
[TD]07/03/2000[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Ian[/TD]
[TD]04/02/2000[/TD]
[TD][/TD]
[TD]Luke[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Emma[/TD]
[TD]01/01/2000[/TD]
[TD][/TD]
[TD]Martha[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Emma[/TD]
[TD]02/02/2000[/TD]
[TD][/TD]
[TD]Matt[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Emma[/TD]
[TD]09/03/2000[/TD]
[TD][/TD]
[TD]Lucy[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]John[/TD]
[TD]03/02/2000[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Luke[/TD]
[TD]04/02/2000[/TD]
[TD][/TD]
[TD]Stacy[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Martha[/TD]
[TD]02/01/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Matt[/TD]
[TD]01/01/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Matt[/TD]
[TD]02/01/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Matt[/TD]
[TD]06/03/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Lucy[/TD]
[TD]02/01/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Lucy[/TD]
[TD]07/03/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Peter[/TD]
[TD]08/03/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Stacy[/TD]
[TD]03/02/2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Where E2 uses a formula which is then dragged down the E column:

SUM(IF(FREQUENCY(INDIRECT(ADDRESS(ROW(OFFSET($B$2,MATCH(D2,$A$2:INDIRECT(ADDRESS(COUNTA(A:A),1)),FALSE)-1,0)),2)):INDIRECT(ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(ROW(OFFSET($B$2,MATCH(D2,$A$2:INDIRECT(ADDRESS(COUNTA(A:A),1)),FALSE)-1,0)),2)),COUNTIF($A$2:INDIRECT(ADDRESS(COUNTA(A:A),1)),D2)-1,0)),2)),INDIRECT(ADDRESS(ROW(OFFSET($B$2,MATCH(D2,$A$2:INDIRECT(ADDRESS(COUNTA(A:A),1)),FALSE)-1,0)),2)):INDIRECT(ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(ROW(OFFSET($B$2,MATCH(D2,$A$2:INDIRECT(ADDRESS(COUNTA(A:A),1)),FALSE)-1,0)),2)),COUNTIF($A$2:INDIRECT(ADDRESS(COUNTA(A:A),1)),D2)-1,0)),2))),1))

Hope this makes sense!

 

[steve the fish]

I think this will work too where the table is in A1:B5 and 'name1' in F1:

=SUM(--(FREQUENCY(IF($A$1:$A$5=F1,$B$1:$B$5),$B$1:$B$5)>0))

 

[tyija1995]

I think this will work too where the table is in A1:B5 and 'name1' in F1:

=SUM(--(FREQUENCY(IF($A$1:$A$5=F1,$B$1:$B$5),$B$1:$B$5)>0))

Hey Steve,

I tested your formula for name1 it appears to work but for name2 it responds with #VALUE ! - Just a heads up!!

I am sure there is a better way out there than what I posted... Probably with VBA!

 

[steve the fish]

Yeah its array formula. CTRL-SHIFT-ENTER not just enter.

 

[tyija1995]

Yeah its array formula. CTRL-SHIFT-ENTER not just enter.

Ahh right! That makes sense