asked on

Looking for unique numbers with several criteria

I need to add an additional criteria to the following formula and I tried but it is not working??
This one works:

{="Unique Numbers in SAP = "&SUM(--(FREQUENCY(IF(Sheet2!C2:C50000<>"",MATCH(Sheet2!C2:C50000,Sheet2!C2:C50000,0)),ROW(Sheet2!C2:C50000)-ROW(Sheet2!C2)+1)>0))}

This one does not work:

{="Unique Numbers in SAP = "&SUM(--(FREQUENCY(IF(,Sheet2!H2:H50000"<>SESA"),IF(Sheet2!C2:C50000<>"",MATCH(Sheet2!C2:C50000,Sheet2!C2:C50000,0))),ROW(Sheet2!C2:C50000)-ROW(Sheet2!C2)+1)>0))}

Ejgil Hedegaard

Try this

="Unique Numbers in SAP = "&SUM(--(FREQUENCY(IF(Sheet2!H2:H50000<>"SESA",IF(Sheet2!C2:C50000<>"",MATCH(Sheet2!C2:C50000,Sheet2!C2:C50000,0))),ROW(Sheet2!C2:C50000)-ROW(Sheet2!C2)+1)>0))

Open in new window

ssblue

ASKER

THe formula works but only if the duplicates are deleted. I need it to work without deleting the duplicates.

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ssblue

ASKER

You are correct, I was running into the issue where one of the dups was SESA and one wasn’t. Not sure how to handle that. Thanks for the help. I will need to do some thinking on this.

ssblue

ASKER

Thanks!

ssblue

ASKER

Could we write it to count unique and then subtract all SESA?

Ejgil Hedegaard

Don't know how to do that, but conditional format could highlight numbers where some have SESA and some don't.
Then filter on the color, and correct.
Unique-numbers.xlsx

ssblue

ASKER

Thanks for the insight and help. I have to do some thinking on it.