Help nesting SUMIF and LEFT or SUMPRODUCT LEFT & AND

I’m trying to sum data where the first 8 characters in column “D” equals “PURCHASE” and the amount in Column “B” is less than 0.
The equation below works for tallying all amounts less than 0 in column B.

=SUMIFS(B2:B6, B2:B6, “<0”)

When I try to test both conditions, I get an error.

=SUMIFS( B2:B6, B2:B6, “<0”, (LEFT(D2:D6,8)),”=PURCHASE”)

I’ve also tried various version of SUMPRODUCT

=SUMPRODUCT((LEFT(D2:D506,8)="PURCHASE")*B2:B506)

=SUMPRODUCT( AND( (LEFT($D$2:$D$506,8)="PURCHASE"), ($B$2:$B$506)<0 ) *$B$2:$B$506)


Can anyone help me?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Try

=SUMPRODUCT((LEFT($D$3:$D$506,8)="PURCHASE")*($B$3:$B$506<0)*($A$3:$A$506>=date1)*($A$3:$A$506<=date2)*$B$3:$B$506)

Date1 & Date2 are the start/end dates

The same way: put the dates in cells (e,g. M1 and M2) so you do not have to change the formula. You can make these cells .named ranges called "Date1" and "Date2" so the formula provided can be used unchanged