Count unique values between date range that match criteria

Question

I am needing to count the number of unique values in column I that meet the following criteria.  Column matches cell A1, column T has a "Y" in it, and column M is between the dates in cells DM1 (5/1/17) & DM3 (5/6/17)

Answer 1

Hi,

Try this array formula (Ctrl+Shift+Enter)

=SUMPRODUCT(1*(FREQUENCY(IF($B$2:$B$7=$A1,IF($T$2:$T$7="Y",IF($M$2:$M$7>=$DM$1,IF($M$2:$M$7<=$DM$3,MATCH($I$2:$I$7,$I$2:$I$7,0))))),ROW($B$2:$B$7)-ROW($C$1))>0))

The entry in cell A1 will be searched in B2:B7.

Hope this helps.

Answer 2

You are welcome.  You must confirm the formula with Ctrl+Shift+Enter (not a simple Enter)

Answer 3

Thanks!  That formula worked for what I was looking for.  I tried to edit the formula to do the same thing with a different list and it keeps giving me a #N/A error when there are values in the list that match the criteria. 

This is what I'm needing this new formula to do:

count unique values in CF

CJ equals A1

CI equals DL38

CQ is between DM1 & DM3 (dates)

Answer 4

Another couple of options, enter below formulas as arrray formulas (CTRL+SHIFT+ENTER):

=SUM(IF(FREQUENCY(IF(B2:B100=A1,IF(T2:T100="Y",IF(M2:M100>=DM1,IF(M2:M100<=DM3,IF(I2:I100<>"",MATCH(I2:I100,I2:I100&"",0)))))),ROW(I2:I100)-ROW(I2)+1),1))

 OR

=SUM(IF(I2:I100<>"",IF((T2:T100="Y")*(M2:M100>=DM1)*(M2:M100<=DM3)*(B2:B100=A1),1/(COUNTIFS(T2:T100,"Y",B2:B100,A1,M2:M100,">="&DM1,M2:M100,"<="&DM3,I2:I100,I2:I100))),0))

Using these formulas even if any value in column I is blank or missing, the formula will not give an error (you may try to delete a unique value in column I matching the criteria and check). The logic of B2:B100 = A1 is presumed (per your statement "Column matches cell A1").

Regards,

Amit Tandon

www.globaliconnect.com

http://twitter.com/AmitTandonExcel