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!

 

[Aladin Akyurek]

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

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. When done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[lmcc007]

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. When done successfully, Excel itself puts a pair of { and } around the formula in recognition.

Do I have to hit C+S+E all the time? Will it do it automatically.

Also, the answer is not correct. I need it to check to see if a number like 7, 9... appeared 3 or more times in the range. Then if so, put "hot"....

 

[lmcc007]

Okay, I info on the Web about =Max(Frequency(IF....



{=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5))))}


Note: this is an array formula and must be entered with Control + Shift + Enter.

How this formula works

This is a tricky formula to understand, so buckle up!

This is way out my range of understanding.

 

[Aladin Akyurek]

Do I have to hit C+S+E all the time? Will it do it automatically.[…]

Only when you edit the formula, otherwise the answer is no.

 

[Keebellah]

It's what they call an array formula,

 

[lmcc007]

It's what they call an array formula,

It's storming here and the lights just came back on.

 

[lmcc007]

Okay. Let me see my manual answer is different.

 

[Keebellah]

The calm after the storm
When you strike Ctrl+Shift+Enter the {} are placed and it converts the formula to an array formula, you only have to do this once only IF you edit the formula.
Once it's there you can drag it down and it works.
ONLY AFTER EDITTING

Google for array formulays, one like that comes up
https://support.office.com/en-us/ar...-formula-e43e12e0-afc6-4a12-bc7f-48361075954d

 

[lmcc007]

The calm after the storm
When you strike Ctrl+Shift+Enter the {} are placed and it converts the formula to an array formula, you only have to do this once only IF you edit the formula.
Once it's there you can drag it down and it works.
ONLY AFTER EDITTING

Google for array formulays, one like that comes up
https://support.office.com/en-us/ar...-formula-e43e12e0-afc6-4a12-bc7f-48361075954d

I did the C+S+E and it worked, but the answer is still different from my manual answer.

Thanks, I have looked up array formulas, SumProduct, Max... a few times already but I am still lost. I can't find any examples like what I am doing. And, of course, I maybe asking the wrong questions since I am new at this. I know I have spent way too much time on this.

Okay, I have gotten off tract somehow. I have confused myself.

1. I have six columns (A-F)
2. C10:F10 is the row I want to compare against the previous 10 rows of number. I am looking to see if any of the numbers in C10:F10 have repeated three or more times in the previous 10 rows (C10:F10).
3. If any of the numbers in C10:F10 have repeated three or more times, put “Hot” and “NH” if they have not.
4. Also, I do not want to include the following numbers when checking for repeats: 4, 6, 15, 19, 25, 27, 30, and 33.

When I do it pen to paper it is simple--i.e., looking at previous 10 numbers to current 4 numbers, and so on.

 

[Keebellah]

Wat you write down try and translate it to vba respecting the vba syntax because that is all it really is, that’s also the way I explain VBA