how to get unique values from column with 5 criteria

04lobopr

New Member
Joined
Sep 22, 2014
Messages
2
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
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,397
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
Joined
Sep 22, 2014
Messages
2
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,936
Messages
5,545,105
Members
410,656
Latest member
Hydraulics
Top