# Sumproduct issue

#### komhs

##### New Member
Hi to all and thanks in advance for the help

I am doing something wrong here and my mind has stucked.
I want to count how many times (for example) the number 2 exists simultaneously in the same row (of columns No 1:No 5) with number 7 when the value in the same row of Secondary column is <=90.

The answer is 4, but obviously I am doing something wrong, because I am getting a zero in every number.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### jasonb75

##### Well-known Member
This is all that stands out.

The reference to J2 and K2 should be \$J\$2 and \$K\$2
The reference to J4 should be J\$4

There may be other problems, but I can't test a formula on a screen capture.

#### komhs

##### New Member
This is all that stands out.

The reference to J2 and K2 should be \$J\$2 and \$K\$2
The reference to J4 should be J\$4

There may be other problems, but I can't test a formula on a screen capture.
You are right. I am sorry. I will add it in xl2bb. Here you are

TZOKER.xlsm
ABCDEFGHIJKLMNOPQR
1TABLE 9TABLE 10
2Νο 1Νο 2Νο 3Νο 4Νο 5no 6Secondary7090
3191112131920Column1Column2Column3Column4Column5Column6Column7Column8Column9
47291617830123456789
515913181233000000400
612714161153101112131415161718
71121314151063000000000
818141517766
9327681167
1012469975How many times each number is in the same row with 7 under a certain condition? It shows 0
1132357780but it is not true. For example number 2 exists 4 times in the same row (except column 6) with 7
12172111318137with the condition (>=0 and <=90) of the Secondary column
133121314159122The number 7 must count how many times exists in the condition (>=0 and <=90) And it is trully 4
Sheet6
Cell Formulas
RangeFormula
I5:Q5I5=SUMPRODUCT((Table9[[Νο 1]:[Νο 5]]=\$I\$2)*(Table9[[Secondary]:[Secondary]]>=\$J\$2)*(Table9[[Secondary]:[Secondary]]<=\$K\$2)*(Table9[[Νο 1]:[Νο 5]]=I\$4))
I7:Q7I7=SUMPRODUCT((Table9[[Νο 1]:[Νο 5]]=\$I\$2)*(Table9[[Secondary]:[Secondary]]>=\$J\$2)*(Table9[[Secondary]:[Secondary]]<=\$K\$2)*(Table9[[Νο 1]:[Νο 5]]=I\$6))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I5Cell Valuetop 5 bottom valuestextNO
I5Cell Valuetop 5 valuestextNO
J5:Q5,I7:Q7Cell Valuetop 5 bottom valuestextNO
J5:Q5,I7:Q7Cell Valuetop 5 valuestextNO

#### Fluff

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQ
1TABLE 9TABLE 10
2Νο 1Νο 2Νο 3Νο 4Νο 5no 6Secondary7090
3191112131920Column1Column2Column3Column4Column5Column6Column7Column8Column9
47291617830123456789
515913181233142011411
612714161153101112131415161718
71121314151063000010210
818141517766
9327681167
1012469975
1132357780
12172111318137
133121314159122
Lists
Cell Formulas
RangeFormula
I5:Q5,I7:Q7I5=SUM(--(MMULT(((\$A\$3:\$E\$13=\$I\$2)+(\$A\$3:\$E\$13=I4))*(\$G\$3:\$G\$13<=\$K\$2)*(\$G\$3:\$G\$13>=\$J\$2),{1;1;1;1;1})>1))

#### komhs

##### New Member

+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQ
1TABLE 9TABLE 10
2Νο 1Νο 2Νο 3Νο 4Νο 5no 6Secondary7090
3191112131920Column1Column2Column3Column4Column5Column6Column7Column8Column9
47291617830123456789
515913181233142011411
612714161153101112131415161718
71121314151063000010210
818141517766
9327681167
1012469975
1132357780
12172111318137
133121314159122
Lists
Cell Formulas
RangeFormula
I5:Q5,I7:Q7I5=SUM(--(MMULT(((\$A\$3:\$E\$13=\$I\$2)+(\$A\$3:\$E\$13=I4))*(\$G\$3:\$G\$13<=\$K\$2)*(\$G\$3:\$G\$13>=\$J\$2),{1;1;1;1;1})>1))

Perefect! Thank you so much. To tell you the truth I have never ever used the MMULT...

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.
I have never ever used the MMULT...
That makes two of us, this is the 1st time for me.

#### komhs

##### New Member
You're welcome & thanks for the feedback.

That makes two of us, this is the 1st time for me.
You're welcome
First time is always special

Replies
3
Views
62
Replies
1
Views
108
Replies
1
Views
129
Replies
3
Views
452
Replies
6
Views
1K

1,148,277
Messages
5,745,807
Members
423,978
Latest member
leodo21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back