Sumproduct issue

komhs

New Member
Joined
Jun 12, 2016
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
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.


1622891741787.png
 

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
Joined
Dec 30, 2008
Messages
14,565
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2016
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
How about
+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))
 
Solution

komhs

New Member
Joined
Jun 12, 2016
Messages
33
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

How about
+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
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2016
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top