how to get unique values from column with 5 criteria

[04lobopr]

trying to get unique claim number between two dates with criteria set to MIG in column L

current formula being used yeilds a N/A error message


=SUM(IF(FREQUENCY(IF(DataSheet!A8:A5000=Banner!K9,IF(DataSheet!K8:K5000>=Banner!C9,IF(DataSheet!K8:K5000<=Banner!G9,IF(DataSheet!L8:L5000="MIG",MATCH(DataSheet!D8:D5000,D8:D5000,0))))),ROW(DataSheet!D8:D5000)-ROW(DataSheet!$D$8))+1,1))

Banner K9 is a text criteria
Banner C9 and G9 are dates

any help is much appreciated

 

[Marcelo Branco]

trying to get unique claim number between two dates with criteria set to MIG in column L

current formula being used yeilds a N/A error message


=SUM(IF(FREQUENCY(IF(DataSheet!A8:A5000=Banner!K9,IF(DataSheet!K8:K5000>=Banner!C9,IF(DataSheet!K8:K5000<=Banner!G9,IF(DataSheet!L8:L5000="MIG",MATCH(DataSheet!D8:D5000,D8:D5000,0))))),ROW(DataSheet!D8:D5000)-ROW(DataSheet!$D$8))+1,1))

Banner K9 is a text criteria
Banner C9 and G9 are dates

any help is much appreciated

Note errors in red

Try (corrections in blue)

=SUM(IF(FREQUENCY(IF(DataSheet!A8:A5000=Banner!K9,IF(DataSheet!K8:K5000>=Banner!C9,IF(DataSheet!K8:K5000<=Banner!G9,IF(DataSheet!L8:L5000="MIG",MATCH(DataSheet!D8:D5000,DataSheet!D8:D5000,0))))),ROW(DataSheet!D8:D5000)-ROW(DataSheet!$D$8)+1),1))

Ctrl+Shift+Enter

Hope this helps

M.

 

[04lobopr]

Note errors in red

Try (corrections in blue)

=SUM(IF(FREQUENCY(IF(DataSheet!A8:A5000=Banner!K9,IF(DataSheet!K8:K5000>=Banner!C9,IF(DataSheet!K8:K5000<=Banner!G9,IF(DataSheet!L8:L5000="MIG",MATCH(DataSheet!D8:D5000,DataSheet!D8:D5000,0))))),ROW(DataSheet!D8:D5000)-ROW(DataSheet!$D$8)+1),1))

Ctrl+Shift+Enter

Hope this helps

M.

Thanks Marcela, this works but yeilds the wrong count

would you be able to advise how to add dates to this formula
. I need it to provide a unqiue count between two dates (date 1 and date2)

=SUM(IF(FREQUENCY(IF(DataSheet!$A$8:$A$5000=Banner!$K$9,IF(DataSheet!$L$8:$L$5000="MIG",DataSheet!$D$8:$D$5000)),IF(DataSheet!$A$8:$A$5000=Banner!$K$9,IF(DataSheet!$L$8:$L$5000="MIG",DataSheet!$D$8:$D$5000)))>0,1))

This gives an accurate count when i have two criteria, but when i add the date criteria it does not work.

Any help is much appreciated. Thank you!!

 

[Marcelo Branco]

Could you post a small data sample for testing purposes?

M.