# 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 PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### 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
76
Replies
2
Views
96
Replies
1
Views
117
Replies
5
Views
156
Replies
5
Views
97