SUMPRODUCT to include negative and positive values

[pnr8uk]

Morning all

I have a problem with my SUMPRODUCT formula.

I have one column in my spreadsheet which contains P&L expressed as -£1.00 or £1.00 the columns are formatted as currency.

However I want to get the total P&L from these columns based on Month and Year (though that is not important in this question)

When I use this formula.

=SUMPRODUCT((MONTH(MatchBetting!A$5:A$1000)=8)*(YEAR(MatchBetting!A$5:A$1000)=2018)*(MatchBetting!T$5:T$1000))

I always end up with a negative value. I know this is incorrect as a SUBTOTAL on the column reveals the true positive amount. Also a calculator too

Any ideas on how I change/adapt/don't use the above formula to sum a column with positive and negative values? I do need the by month function (so quite important after all)

Many thanks for any help

P

 

[JackDanIce]

Welcome to the board. This works for me:

=SUMPRODUCT(--(MONTH(MatchBetting!$A$5:$A$1000)=8),--(YEAR(MatchBetting!$A$5:$A$1000)=2018),MatchBetting!$T$5:$T$1000)

 

[pnr8uk]

Welcome to the board. This works for me:

=SUMPRODUCT(--(MONTH(MatchBetting!$A$5:$A$1000)=8),--(YEAR(MatchBetting!$A$5:$A$1000)=2018),MatchBetting!$T$5:$T$1000)

That works beautifully thank you for this, sorry for the delay in replying. Very helpful indeed.

Cheers

 

[JackDanIce]

You're welcome