Arrays - counting unique rows with criteria

[davidepstein22]

Hi

I have a large s/s with 30 columns by 800 rows. I need to count the unique ids between a date range provided they are of the desired type.

Column A = Type
Column B = id
Column C = finish date

For example, I want to count values where:
column A="xyz"
column B = has multiple values that are duplicated on many rows (non unique).
Column C = finish date between 10/1/14 and 10/31/14.

I want to count the number of unique occurances of column B (id) provided the finish date is between 10/1/14 and 10/31/14.

I thought this would be a good starting place, but I am stuck (array) {=sum(1/countif(b:b))}

I would appreciate some help!!


Thanks,
Dave

 

[Domenic]

To return a count of unique ID's in Column B, where the corresponding cell in Column A equals "xyz" and the corresponding cell in Column C contains a date between 10/1/14 and 10/31/14, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(A2:A100="xyz",IF(C2:C100>=DATE(2014,10,1),IF(C2:C100<=DATE(2014,10,31),IF(B2:B100<>"",MATCH("~"&B2:B100,B2:B100&"",0))))),ROW(B2:B100)-ROW(B2)+1)>0,1))

Adjust the ranges, accordingly.

Hope this helps!

 

[dankeykang]

I've tried your formula without any luck. Keeps giving me an error.

The following is my data

Column B has transaction numbers (there can be multiple exchanges per transaction so the transaction number can come up multiple times)
Column E has the the persons name who did the transaction
Column F has the date it occurred
Start Date is in k1
End date is in k2


I have over 10k records that I need to get the unique record count for each person per a particular time frame.

Column B Column E Column F
[TABLE="width: 306"]
<tbody>[TR]
[TD="align: right"]5456298[/TD]
[TD]John[/TD]
[TD="align: right"]1/3/2016[/TD]
[/TR]
[TR]
[TD="align: right"]5456306[/TD]
[TD]Bob[/TD]
[TD="align: right"]1/5/2016[/TD]
[/TR]
[TR]
[TD="align: right"]5456296[/TD]
[TD]John[/TD]
[TD="align: right"]1/3/2016[/TD]
[/TR]
[TR]
[TD="align: right"]5456206[/TD]
[TD]John[/TD]
[TD="align: right"]1/3/2016[/TD]
[/TR]
[TR]
[TD="align: right"]5456199[/TD]
[TD]Jennifer[/TD]
[TD="align: right"]1/12/2016[/TD]
[/TR]
[TR]
[TD="align: right"]5456199[/TD]
[TD]Jennifer[/TD]
[TD="align: right"]1/3/2016[/TD]
[/TR]
[TR]
[TD="align: right"]5456204[/TD]
[TD]Trevor[/TD]
[TD="align: right"]1/30/2016[/TD]
[/TR]
</tbody>[/TABLE]

For this sample, I need to get the unique transaction count per person between 1/1/2016 and 1/11/2016.

Any tips?

 

[Domenic]

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

 

[dankeykang]

I did and it looks like I missed a comma but it seems like it's counting the unique times the persons name comes in, not the transaction number. Which is fine as the number is the same, however, it's not taking into account the defined date range.

The formula i entered is as follows

=SUM(IF(FREQUENCY(IF(Table1[transaction]<>"",IF($k$1>=Table1[Date opened],IF(Table1[Date opened]<=$k$2,IF(Table1[name]=L5,MATCH("~"&Table1[transaction],Table1[transaction]&"",0))))),ROW(Table1[transaction])-ROW($B$2)+1),1))

 

[dankeykang]

Another note, after deleting all of the sample data outside of 1 person, it's giving me a value of 1 regardless of the persons name/date.

 

[Domenic]

I don't think you've set up the criteria for your dates correctly. Try...

=SUM(IF(FREQUENCY(IF(Table1[transaction]<>"",IF(Table1[Date opened]>=$k$1,IF(Table1[Date opened]<=$k$2,IF(Table1[name]=L5,MATCH("~"&Table1[transaction],Table1[transaction]&"",0))))),ROW(Table1[transaction])-MIN(ROW(Table1[transaction]))+1),1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[dankeykang]

YOU'RE THE MAN! THANKS SO MUCH! I've been trying to solve this since march!