Sumproduct with subtraction

[JamesTucker]

Hello

I'm am trying to get sumproduct to subtract values in a column that match the criteria of the formula

I have a check register that and each entry has a category, Payee, Spent, Received, Balance

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]CATEGORY[/TD]
[TD]PAYEE[/TD]
[TD]SPENT[/TD]
[TD]RECEIVED[/TD]
[TD]BALANCE[/TD]
[/TR]
[TR]
[TD]Venue[/TD]
[TD]Tom thumb[/TD]
[TD]125.00[/TD]
[TD][/TD]
[TD]500.00[/TD]
[/TR]
[TR]
[TD]Promotion[/TD]
[TD]Pro forma[/TD]
[TD]200.00[/TD]
[TD][/TD]
[TD]300.00[/TD]
[/TR]
[TR]
[TD]Promotion[/TD]
[TD]Deposit[/TD]
[TD][/TD]
[TD]350.00[/TD]
[TD]650.00[/TD]
[/TR]
[TR]
[TD]Dues[/TD]
[TD]Deposit[/TD]
[TD][/TD]
[TD]1000.00[/TD]
[TD]1650.00[/TD]
[/TR]
</tbody>[/TABLE]

all of my values are positive, so the balance column adds and subtracts, rather than adding negative values.

I have been using SUMPRODUCT to keep track of the totals spend in each category, but now I have a problem because we purchased and sold T shirts

=SUMPRODUCT((Category=Promotion)*(Spent)*(Received)) This will just ADD values in the received column because all of my values are positive.

=SUMPRODUCT((Category=Promotion)*(Spent)-(Received)) This formula seems to subtract ALL of the values in the received column, not just the ones attributed to PROMOTION


Thanks for any help

 

[Fluff]

Hi & welcome to MrExcel.
How about
=SUMPRODUCT((A2:A5="Promotion")*(C2:C5-D2:D5))