Looking for unique numbers with several criteria

ssblue
ssblue used Ask the Experts™
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

ssblueCoordinator

Author

Commented:
THe formula works but only if the duplicates are deleted.  I need it to work without deleting the duplicates.
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ssblueCoordinator

Author

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.
ssblueCoordinator

Author

Commented:
Thanks!
ssblueCoordinator

Author

Commented:
Could we write it to count unique and then subtract all SESA?
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
ssblueCoordinator

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial