# Looking for unique numbers with several criteria

on
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))}
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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))
``````
Coordinator

Commented:
THe formula works but only if the duplicates are deleted.  I need it to work without deleting the duplicates.
Commented:
In attached numbers 1 to 12 are duplicated 3 times in column C.
There are several with SESA in column H, but only number 4 and 9 has it for all, so the count is 10.
What is wrong.
Unique-numbers.xlsx
Coordinator

Commented:
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.
Coordinator

Commented:
Thanks!
Coordinator

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

Commented:
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
Coordinator

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

Do more with