Sumproduct to addup only the rows which addup to a positive value

[prakasharadhana]

Using sumproduct to arrive at sum of rows of an array which satisfy the below:
1. the column condition
2. the row condition
3. the sum of the row should be a positive value
Thank you.

Excel sheet attached, snapshot below:

 

[bosco_yip]

Based on your formula, just add a condition checking for non-negative value rows,

so,

In E17, enter formula :

=SUMPRODUCT(C4:G9,(C3:G3<=E12)*(B4:B9=F12)*(COUNTIF(OFFSET(C3:G3,ROW(C4:G9)-ROW(C3),0),">=0")=5))

 

[prakasharadhana]

@bosco_yip
I want the formula to be dynamic, the above is just test data, I want to apply it to much larger data.

Also if you could explain what the last part of the formula does, it would make it easier for me. Thanks in advance.

 

[prakasharadhana]

@bosco_yip
So what I found the problem now is the above formula ignores any row that has a negative value, but what I want is to ignore the row only if the sum of the values is negative. I hope you get it.

 

[prakasharadhana]

Below snapshot for a better understanding

 

[bosco_yip]

Below snapshot for a better understanding
View attachment 74534

Then, adding an Array 2 table for comparison

Changed the E17 formula to :

=SUMPRODUCT(C4:G9,(C3:G3<=E12)*(B4:B9=F12)*(MMULT(C4:INDEX(C9:G9,MATCH(E12,C3:G3,0)),ROW(INDIRECT("1:"&MATCH(E12,C3:G3,0)))^0)>=0))

 

[Excel Wizard]

Please try

=SUM(--TEXT(MMULT((C3:G3<=E12)*(B4:B9=F12)*C4:G9,TRANSPOSE(C3:G3)^0),"0;\0"))
with Ctrl+Shift+Enter

or
=SUMPRODUCT(--TEXT(MMULT((C3:G3<=E12)*(B4:B9=F12)*C4:G9,ROW(A1:INDEX(A1:A20,COLUMNS(C3:G3)))^0),"0;\0"))
normal Enter

 

[prakasharadhana]

Then, adding an Array 2 table for comparison

Changed the E17 formula to :

=SUMPRODUCT(C4:G9,(C3:G3<=E12)*(B4:B9=F12)*(MMULT(C4:INDEX(C9:G9,MATCH(E12,C3:G3,0)),ROW(INDIRECT("1:"&MATCH(E12,C3:G3,0)))^0)>=0))

View attachment 74545

Hi, this works just fine...thanks a lot ...though I did not understand it completely.

 

[prakasharadhana]

Please try

=SUM(--TEXT(MMULT((C3:G3<=E12)*(B4:B9=F12)*C4:G9,TRANSPOSE(C3:G3)^0),"0;\0"))
with Ctrl+Shift+Enter

or
=SUMPRODUCT(--TEXT(MMULT((C3:G3<=E12)*(B4:B9=F12)*C4:G9,ROW(A1:INDEX(A1:A20,COLUMNS(C3:G3)))^0),"0;\0"))
normal Enter

Hi, I could not understand the cell reference A1:A20 in the sumproduct formula above.