A Complex Sumif function...

[chrisdols]

Here's a challenge:

I'd like to sum the differences between two columns (B-A) but only for those rows where column B's value is greater than Column A's:

[TABLE="width: 200"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

So my desired formula would produce '5' for this array, summing the difference for rows 3 and 4 because they are the ones that satisfy the criteria.

I can't make 'sumif' work for me b/c I don't know how to make it seek a neighboring column for its criteria. I have a spreadsheet with hundreds of columns so it can't be just as simple as adding an additional column with an 'if' statement before summing them. I need the single formula to perform both steps.

Thank you,
Chris

 

[Weazel]

maybe....

=SUMPRODUCT(--(B2:B5>A2:A5),B2:B5-A2:A5)

 

[oldbrewer]

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="class: xl22, width: 64"] [/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl23, width: 64"]6[/TD]
[TD="class: xl23, width: 64"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl23, width: 64"] [/TD]
[TD="class: xl23, width: 64"]7[/TD]
[TD="class: xl23, width: 64"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl23, width: 64"] [/TD]
[TD="class: xl23, width: 64"]5[/TD]
[TD="class: xl23, width: 64"]7[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl23, width: 64"] [/TD]
[TD="class: xl23, width: 64"]5[/TD]
[TD="class: xl23, width: 64"]8[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl23, width: 64"] [/TD]
[TD="class: xl23, width: 64"]4[/TD]
[TD="class: xl24"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl23, width: 64"]1[/TD]
[TD="class: xl23, width: 64"]6[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl23, width: 64"]2[/TD]
[TD="class: xl23, width: 64"]7[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]formula for cell giving 2 in third column (cell D4)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]=IF(C4>B4,(C4-B4)+MAX($D$2:D3),"")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[chrisdols]

Like I mentioned, I cannot use another column.

 

[chrisdols]

Looks like this will work. Thanks!

maybe....

=SUMPRODUCT(--(B2:B5>A2:A5),B2:B5-A2:A5)