Sumproduct issue

komhs

New Member
Joined
Jun 12, 2016
Messages
44
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
Solution
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...
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top