SUM if Row+Column > X

[indygo]

hi guys,

I'm having a little brain freeze here

I would like sum values in cross table xy based on sum of x and y.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]0.10[/TD]
[TD="align: center"]0.12[/TD]
[TD="align: center"]0.25[/TD]
[TD="align: center"]0.30[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0.05[/TD]
[TD="align: center"]0.10[/TD]
[TD="align: center"]0.32[/TD]
[TD="align: center"]0.40[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]0.14[/TD]
[TD="align: center"]0.13[/TD]
[TD="align: center"]0.10[/TD]
[TD="align: center"]0.15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0.18[/TD]
[TD="align: center"]0.11[/TD]
[TD="align: center"]0.16[/TD]
[TD="align: center"]0.21[/TD]
[/TR]
</tbody>[/TABLE]


For example:

sum values if Column + Row >7.5. We know that there is only one such scenario 4+4 so it equals 0.21
sum values if Column + Row < 4.00. That would be 1-1,2-1,1-2 so 0.10+0.05+0.12 = 0.27

What formula could calculate this automatically based on my X value?

I tried something like =SUMIF(A1:E5,A2:A5+B1:E1>B7,B2:E5) but that doesn't work.


Thanks in advance

 

[Gerald Higgins]

If I was doing this, I would use a helper table, like this.

Let's make some assumptions.
Your existing table is in the range A1:E5 (so that the bottom right value of 0.21 is in cell E5).
Your target value of 7.5 or 4.00 or whatever is held in cell F1.
Your helper table is in the range G1:K5.

In cell H2 put this formula . . .
=IF((H$1+$G2)>$F$1,B2,0)

Then copy this across to the range H2:K5.

Then use this formula . . .
=sum(H2:K5)
which should give you the desired result.

Obviously adapt all cell and range references as required.

 

[indygo]

If I was doing this, I would use a helper table, like this.

Let's make some assumptions.
Your existing table is in the range A1:E5 (so that the bottom right value of 0.21 is in cell E5).
Your target value of 7.5 or 4.00 or whatever is held in cell F1.
Your helper table is in the range G1:K5.

In cell H2 put this formula . . .
=IF((H$1+$G2)>$F$1,B2,0)

Then copy this across to the range H2:K5.

Then use this formula . . .
=sum(H2:K5)
which should give you the desired result.

Obviously adapt all cell and range references as required.

That's clever, thanks a lot.

 

[Peter_SSs]

You can do it directly like this.

<b>Sum</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:26px;" /><col style="width:56px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">0.1</td><td style="font-size:10pt; text-align:right; ">0.12</td><td style="font-size:10pt; text-align:right; ">0.25</td><td style="font-size:10pt; text-align:right; ">0.3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">0.21</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">0.05</td><td style="font-size:10pt; text-align:right; ">0.1</td><td style="font-size:10pt; text-align:right; ">0.32</td><td style="font-size:10pt; text-align:right; ">0.4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">0.27</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">0.14</td><td style="font-size:10pt; text-align:right; ">0.13</td><td style="font-size:10pt; text-align:right; ">0.1</td><td style="font-size:10pt; text-align:right; ">0.15</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">0.18</td><td style="font-size:10pt; text-align:right; ">0.11</td><td style="font-size:10pt; text-align:right; ">0.16</td><td style="font-size:10pt; text-align:right; ">0.21</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=SUMPRODUCT(<span style=' color:008000; '>(B1:E1+A2:A5>7.5)</span>*B2:E5)</td></tr><tr><td >G3</td><td >=SUMPRODUCT(<span style=' color:008000; '>(B1:E1+A2:A5<4)</span>*B2:E5)</td></tr></table></td></tr></table>