# how to get unique values from column with 5 criteria

#### 04lobopr

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Marcelo Branco

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
Could you post a small data sample for testing purposes?

M.

Replies
5
Views
75
Replies
2
Views
95
Replies
1
Views
115
Replies
5
Views
156
Replies
5
Views
95