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!

 

[Keebellah]

Hi, welcome to the forum.
Use a helper column and do the sum there
in this helper column you do the calculations and exceptions and the sum of that column is the result.
If you want more explanation, attach a dummy file with non-private non-sensitive data, makes it easier to work with what YOU have than to invent something.

 

[lmcc007]

I am trying to find out how to attach a file. What do I hit?

 

[Fluff]

You cannot upload files to this site, but there are tools available to enable you to post sample data to the thread.
You can find a link to those tools in my signature

 

[Keebellah]

Yeah I forgot that this site is not as the others.
Not my favourite either

 

[lmcc007]

You cannot upload files to this site, but there are tools available to enable you to post sample data to the thread.
You can find a link to those tools in my signature

I don't see the link.

 

[lmcc007]

Yeah I forgot that this site is not as the others.
Not my favourite either

I think there is a problem with If(SumProduct... because when tested the CountIf by it self (see below), it works and give me an accurate answer.


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")</pre>
But I still need to say that if a number appears three or more times put "Hot" and if not "NH".

 

[Fluff]

I don't see the link.

Have a look here

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IF(MAX(FREQUENCY(IF(ISNA(MATCH($C$1:$F$10,{4;6;15;19;25;27;30;33},0)),IF(ISNUMBER($C$1:$F$10),$C$1:$F$10)),$C$1:$F$10))>=3,"Hot","NH")

Hope this is what you want.

 

[lmcc007]

Control+shift+enter, not just enter:

=IF(MAX(FREQUENCY(IF(ISNA(MATCH($C$1:$F$10,{4;6;15;19;25;27;30;33},0)),IF(ISNUMBER($C$1:$F$10),$C$1:$F$10)),$C$1:$F$10))>=3,"Hot","NH")

Hope this is what you want.

Thank you, but I get an error message: #VALUE !