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