SUM based on values of two different columns

Question

I'd like to sum a column based on the text value of another column as well as whether another column is non-blank.

The image I've attached, but cannot embed yet shows an example of what I need to do.

I have a list of different items in Column B (currently 4 different items, example shows only 2 for briefness, items A1 and B1). Column A has the totals that need to be summed depending on the item that is chosen in column B. Column C is the date that the item in column B was last changed, meaning that I need to sum Column A from the row just below where the last entry in Column C was placed.

Hope this is clear and complete. I've tried different combinations of SUMIFS for summing, INDEX to find the row in Column C that I need to start summing. I can't seem to put together the sums and where I want the sums to start and restart depending on the date entries. It is meant to keep a running total in the spreadsheet of active quantities through each unique item according to the dates.

Below is the sum equation I have but does not act correctly between dates entered.

=SUMIFS($A2:$A11,$B2:$B11,"*A1*",$C2:$C11,"")

The below equation was in an effort to find the row with the last date entered in Column C based on Column B item. However, it finds the first entry with a date attached to it and I would really need the last instance of the item with a date attached to it to build a proper range to put into my SUMIFS equation.

=INDEX($C2:$C11,MATCH("A1",$B2:$B11,0))

I've done quite a bit of searching on here but have only found bits and pieces and cannot make the connection, if someone can make sense of this please let me know if you have any solutions, thanks!

Answer 1

Use this:

=SUMPRODUCT((ROW($A$2:$A$11)>AGGREGATE(14,6,ROW($A$2:$A$11)/(($B$2:$B$11=E2)*($C$2:$C$11<>"")),1))*($B$2:$B$11=E2),$A$2:$A$11)

It will sum all like Bs after the last cell in C for that B that is not blank.