sumif help

[cjwally4]

Is there a sumif formula that will sum
<table width="514" border="0" cellpadding="0" cellspacing="0"><col style="width: 23pt;" width="30"> <col style="width: 36pt;" width="48"> <col style="width: 39pt;" width="52"> <col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl63" style="height: 12.75pt; width: 23pt;" width="30" height="17">A</td> <td class="xl63" style="width: 36pt;" width="48">B</td> <td class="xl63" style="width: 39pt;" width="52">C</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">a</td> <td align="right">10</td> <td align="right">8</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">b</td> <td align="right">5</td> <td align="right">16</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">c</td> <td align="right">8</td> <td align="right">15</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">d</td> <td align="right">3</td> <td align="right">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">e</td> <td align="right">12</td> <td align="right">5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">f</td> <td align="right">6</td> <td align="right">10</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td colspan="7" style="">sum the total where the value in column C is greater than the value in colum b</td> </tr> </tbody></table>

 

[tony80]

It can be done with a SUMPRODUCT:

=SUMPRODUCT(C1:C6,--(C1:C6>B1:B6))

 

[GK039]

Try

=SUM(IF(C1:C10>B1:B10,C1:C10,0))

confirmed with Ctrl + Shift + Enter

George

Is there a sumif formula that will sum
<table width="514" border="0" cellpadding="0" cellspacing="0"><col style="width: 23pt;" width="30"> <col style="width: 36pt;" width="48"> <col style="width: 39pt;" width="52"> <col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl63" style="height: 12.75pt; width: 23pt;" width="30" height="17">A</td> <td class="xl63" style="width: 36pt;" width="48">B</td> <td class="xl63" style="width: 39pt;" width="52">C</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">a</td> <td align="right">10</td> <td align="right">8</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">b</td> <td align="right">5</td> <td align="right">16</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">c</td> <td align="right">8</td> <td align="right">15</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">d</td> <td align="right">3</td> <td align="right">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">e</td> <td align="right">12</td> <td align="right">5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">f</td> <td align="right">6</td> <td align="right">10</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td colspan="7" style="">sum the total where the value in column C is greater than the value in colum b</td> </tr> </tbody></table>

 

[barry houdini]

What do you want to sum? like this perhaps

=SUMPRODUCT((C2:C10>B2:B10)+0,C2:C10-B2:B10)

that will sum the differences between C and B only when the difference is positive

 

[GK039]

Hello Barry,

This is much better than my solution. Could you pls explain why the +0 in your formula?

Thanks George

What do you want to sum? like this perhaps

=SUMPRODUCT((C2:C10>B2:B10)+0,C2:C10-B2:B10)

that will sum the differences between C and B only when the difference is positive

 

[cjwally4]

the winner is SUMPRODUCT(C2:C7,--(C2:C7>B2:B7))
where did you figure that out, great stuff