How to add values for amount that are higher than same row in another column

[newtoexcel86]

I have two columns (let's say column A and C), i would like to add the values in column A only if they are higher than the ones in Column C in the same row... how could i do that?

The sheet looks something like this, i would like to add the values in the first column only if they are higher than the ones in the third column and then of course get the sum of them... i would also like to know how many were there in total, but i guess i could do that with a count formula i just need the parameters.. please help

<table x:str="" style="border-collapse: collapse; width: 448px; height: 330px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 74pt;" width="98"> <col style="width: 76pt;" width="101"> <col style="width: 79pt;" width="105"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 74pt;" x:num="67100" width="98" height="17"> $ 67,100.00 </td> <td class="xl23" style="border-left: medium none; width: 76pt;" x:num="110000" width="101"> $ 110,000.00 </td> <td class="xl24" style="border-left: medium none; width: 79pt;" x:num="165100" width="105"> $ 165,100.00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="border-top: medium none; height: 12.75pt;" x:num="146034" height="17"> $ 146,034.00 </td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="239400"> $ 239,400.00 </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="220100"> $ 220,100.00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="border-top: medium none; height: 12.75pt;" x:num="90184.6875" height="17"> $ 90,184.69 </td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="147843.75"> $ 147,843.75 </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="168500"> $ 168,500.00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="border-top: medium none; height: 12.75pt;" x:num="51011.25" height="17"> $ 51,011.25 </td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="83625"> $ 83,625.00 </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="95100"> $ 95,100.00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="border-top: medium none; height: 12.75pt;" x:num="89860.32" height="17"> $ 89,860.32 </td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="147312"> $ 147,312.00 </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="198200"> $ 198,200.00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="border-top: medium none; height: 12.75pt;" x:num="159121.359375" height="17"> $ 159,121.36 </td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="260854.6875"> $ 260,854.69 </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="26000"> $ 26,000.00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="border-top: medium none; height: 12.75pt;" x:num="129941.4375" height="17"> $ 129,941.44 </td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="213018.75"> $ 213,018.75 </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="235700"> $ 235,700.00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="border-top: medium none; height: 12.75pt;" x:num="88869.375" height="17"> $ 88,869.38 </td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="145687.5"> $ 145,687.50 </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="95600"> $ 95,600.00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="border-top: medium none; height: 12.75pt;" x:num="27772.537499999999" height="17"> $ 27,772.54 </td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="45528.75"> $ 45,528.75 </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="43000"> $ 43,000.00 </td> </tr> </tbody></table>

 

[barry houdini]

You can use SUMPRODUCT for a count.....

=SUMPRODUCT(--(A2:A100>C2:C100))

and then modify for the sum....

=SUMPRODUCT(--(A2:A100>C2:C100),A2:A100)

 

[newtoexcel86]

that those seem to work but i forgot one little detail... in column c there is some blank cells and it seems to sum those up also and i would like for those not to be added.. thanks in advance

 

[lenze]

Make a small addition to Barry's formula

=SUMPRODUCT(--(A2:A100>C2:C100),--(C2:C100<>"")*A2:A100)

lenze

 

[newtoexcel86]

that gives me a #value error...

 

[barry houdini]

small syntax change, take out the * and use a comma.....

=SUMPRODUCT(--(A2:A100>C2:C100),--(C2:C100<>""),A2:A100)

 

[lenze]

Interesting, Barry! I originally wrote the formula in a sheet with the comma, and Excel told me I had an error and corrected it to the * , which DOES work. I now, however, just re-entered with the comma and Excel took it this time and it also works. Strange
lenze

 

[newtoexcel86]

you guys are awesome, thank you so much....

 

[newtoexcel86]

but that was interesting for me also, i thought that was the syntax error and i tried it myself and it changed it for * and then when Barry told me to do it I tried again and it accepted it...
strange indeed

Thanks again

Best regards,
Javi

 

[barry houdini]

Hello Lenze,

using * might work.......but it fails if A2:A100 contains any text - even formula blanks (because you'd be multiplying by a text value)