Sum Countif formula but ignore duplicates

[TiLa]

Hello everyone,
I found a good formula on this site a while ago:
=SUM(COUNTIF(B5:G5,NUMBERS)) cse

but I'd like to know if it's possible for this formula to ignore duplicates.

It's a sheet for Lottery - B5:G5 are the 6 numbers chosen, and the NUMBERS is a named range of lines where we key in the actual lotto numbers each week. It counts how many of your numbers have come out.
Since there can be multiples of the same numbers week on week for the lottery, this formula counts those duplicates, but I'm hoping someone can help with amending this formula to ignore them and only count unique entries.

Thanks

[zbor]

Can you upload example workbook?

[NBVC]

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER

[TiLa]

I have attached an example sheet.
Basically I just want to count the Green Cells - also tried to find a CountColor formula but I think my works version of Excel is too old for that, and I'm not experienced in VB at all either so maybe I was also doing some thing funny!

Tried the Count / Frequency formula but that didn't seem to work.

[bebo021999]

I have attached an example sheet.
Basically I just want to count the Green Cells - also tried to find a CountColor formula but I think my works version of Excel is too old for that, and I'm not experienced in VB at all either so maybe I was also doing some thing funny!

Tried the Count / Frequency formula but that didn't seem to work.

@TiLa
You should create a helper area for Numbers where the data ignore duplicates.
Ex: K9:P11
In K9:

Please Login or Register  to view this content.

Copy down and across to fill K9:P11.
The name NUMBERS should be edited to helper area

Please Login or Register  to view this content.

[TiLa]

Thanks very much bebo, that worked perfectly.
V interesting formula btw - I'll keep that on my list of cool formulas