IF SUMPRODUCT COUNTIFF are giving me the wrong answer

[lmcc007]

A dummy/newbie needs some help.

My code is not working properly. After manually checking, I see that my code is not working. I can’t figure out what’s wrong.

I am trying to do the following:

1. I am looking at C1:F10 for duplicate numbers.

2. If the cells have one of the following numbers, do not count them—just ignore them:

4, 6, 15, 19, 25, 27, 30, and 33​

3. I am looking for duplicates that appear three or more times.

4. If three or more duplicates are found, put “Hot,” and if not put “NH”

=IF(SUMPRODUCT(COUNTIFS($C1:$F10,"<>4",$C1:$F10,"<>6",$C1:$F10,"<>15",$C1:$F10,"<>19",$C1:$F10,"<>25",$C1:$F10,"<>27",$C1:$F10,"<>30",$C1:$F10,"<>33")-2)>0, "Hot", "NH")

Thanks!

 

[lmcc007]

Do you want to continue or have a short time out?

The last row is row 21 for the data you posted.

Are you wanting the following;

Compare $C$21:$F$21 with C1:F10 and put the result in H10?
Compare $C$21:$F$21 with C2:F11 and put the result in H11?
Compare $C$21:$F$21 with C3:F12 and put the result in H12?
… and so on?

C21:F21 with c12:F20. I am trying to compare the last 10 previous drawings--meaning, I am looking at current date going back a few years trying to analyze the lottery drawings. Therefore, there are hundreds of drawings to review.

When I do it manually, e.g.:

1. Saturday's, 9/8/2018, the winning numbers were 2-8-22-32.

2. I write them down.

3. Count back 10 drawings.

4. Then I look at the last 10 drawings to see if the current winning numbers hit three or more times.

5. If the winning numbers include 4, 6, 15, 19, 25, 27, 30, and 33, I do not include them because they are considered base numbers (already used).

6. If one of the winning numbers has hit three or more times within the last 10 drawings, I put "Hot." (That way I can see if I can include or exclude them--basically trying to find out if it makes a difference.)