Sumproduct function, subtracting based on condition met

**fiBer** · 03-06-2011, 02:26 AM

Hi All,

I am using the sumproduct function as in the attached spreadsheet, in the summary calculations (bottom in yellow).

For the expenses summary, I wish to add the following to my function:
For example the [Food] criteria: function calculates as it does at the moment, however, if there is a figure in the "Share Amount" column (i.e. a value >0), the function will subtract this amount from the [Food] total.

So, because rows 3 and 11 have a figure greater than 0, the total below should be less $87.

I hope I have explained the requirements correctly?

Any help is greatly appreciated.
Rob

**canapone** · 03-06-2011, 02:53 AM

Hi,in G25 you could try

=SUMPRODUCT(--(LEFT($J$3:$J$79;6)="[Food]");$C$3:$C$79-$H$3:$H$79)

Hope it helps

**DonkeyOte** · 03-06-2011, 02:56 AM

Rob, based on the revised requirements:

Please Login or Register  to view this content.

FWIW, in terms of the original formulae (ie prior to above requirement) you would have been better off using SUMIF than SUMPRODUCT as the former is more efficient.

If you wanted you could store the criteria strings in an adjacent column and replace the constant [Food] with reference to the criteria cell - this will allow you to copy & paste accordingly without needing to adjust remaining formulae.

**fiBer** · 03-06-2011, 04:19 AM

Canapone - thanks, that's perfect! I didn't realise it was so simple (thought I had to integrate an IF statement in there).

DonkeyOte - also thanks, I have little experience with SUMIF however have begun to use it in other workbooks so I might try experiment with it in this