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]

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.

Care to post the data you have along with the expected result?

 

[Keebellah]

And, (maybe I missed it) where do you want the results "Hot" and "NH" to be placed?

 

[lmcc007]

A 9/18/2014	B	C 7	D 14	E 17	F 22	G	H NH
9/22/2014		8	9	22	34		Hot
9/25/2014		1	4	11	31		Hot
9/29/2014		5	17	18	27		Hot
10/2/2014		4	18	30	32
10/6/2014		3	9	16	28
10/9/2014		3	20	22	30
10/13/2014		11	23	26	30
10/16/2014		22	31	32	33
10/20/2014		6	9	12	28
10/23/2014		1	9	18	19
10/27/2014		9	15	32	34
10/30/2014		2	3	28	34
11/3/2014		12	15	21	27
11/6/2014		2	14	27	35
11/10/2014		9	19	26	33
11/13/2014		1	12	27	33
11/17/2014		1	10	17	28
11/20/2014		9	19	30	34
11/24/2014		4	14	22	30
11/27/2014		5	15	18	24

<tbody>
</tbody>

1. The last row is the row that I want to compare against the previous 10 rows of number.

2. I am looking to see if any of the numbers in the last row (5, 15, 18, 24) have repeated three or more times in the past.

3. If any of the numbers in the last row 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.

 

[lmcc007]

And, (maybe I missed it) where do you want the results "Hot" and "NH" to be placed?

Column H10 (the cell I am currently in and moving downward)

 

[lmcc007]

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

Huh? I am a beginner/dummy just trying to use Excel to do something that I do manually.

 

[Aladin Akyurek]

A 9/18/2014	B	C 7	D 14	E 17	F 22	G	H NH
9/22/2014		8	9	22	34		Hot
9/25/2014		1	4	11	31		Hot
9/29/2014		5	17	18	27		Hot
10/2/2014		4	18	30	32
10/6/2014		3	9	16	28
10/9/2014		3	20	22	30
10/13/2014		11	23	26	30
10/16/2014		22	31	32	33
10/20/2014		6	9	12	28
10/23/2014		1	9	18	19
10/27/2014		9	15	32	34
10/30/2014		2	3	28	34
11/3/2014		12	15	21	27
11/6/2014		2	14	27	35
11/10/2014		9	19	26	33
11/13/2014		1	12	27	33
11/17/2014		1	10	17	28
11/20/2014		9	19	30	34
11/24/2014		4	14	22	30
11/27/2014		5	15	18	24

<tbody>
</tbody>

1. The last row is the row that I want to compare against the previous 10 rows of number.

2. I am looking to see if any of the numbers in the last row (5, 15, 18, 24) have repeated three or more times in the past.

3. If any of the numbers in the last row 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.

What do you mean by previous 10? Which rows are involved regarding the NH result you posted? The same question for the successive Hot results...

 

[lmcc007]

10 rows back. C10:F10, I will look at rows C1:F10. I tried to go backward--i.e., F10:C1 but couldn't. When I do it manually I count back 10 rows to see if the current numbers has hit three or more times in the last 10 drawings excluding 4, 6, 15, 19, 25, 27, 30, and 33.

 

[Aladin Akyurek]

10 rows back. C10:F10, I will look at rows C1:F10. I tried to go backward--i.e., F10:C1 but couldn't. When I do it manually I count back 10 rows to see if the current numbers has hit three or more times in the last 10 drawings excluding 4, 6, 15, 19, 25, 27, 30, and 33.

C1:F10 includes C10:F10!..

 

[lmcc007]

C1:F10 includes C10:F10!..

Yeah, it might be wrong. I've worked on it too long, so I may be missing something or doing something wrong.

 

[Aladin Akyurek]

Yeah, it might be wrong. I've worked on it too long, so I may be missing something or doing something wrong.

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?