Conditional row subtraction in table

0
votes

I have an m x 4 table consisting of the YTD sales per product per month across divisions A to D. I would like to add a column, which calculates the monthly sales per product per month across the divisions.

There cannot be two rows where month, division and product is the same, hence each row is unique.

I have tried to use SUMIFS to index the value, but the part that causes me trouble is that a product can appear mid-year, and a product can also have sales for 3 months and then no sales for 2 months before reappearing again.

Adding multiple “assisting” columns is okay. I cannot use VBA for this particular task.

Thank you in advance! enter image description here

2
excelexcel-formula

2 Answers

1
votes

Because the sales are year to date, you only want to subtract the most recent matching amount from the current amount. One way of finding the most recent matching amount is to use the Large function of Aggregate to get the row number of the last matching row:

=D2-IFERROR(INDEX(D:D,AGGREGATE(14,6,ROW(D$1:D1)/((B$1:B1=B2)*(C$1:C1=C2)),1)),0)

enter image description here

0
votes

You can use following array formula:

{=D2-SUMPRODUCT(IFERROR((B2=$B$1:B1)*(C2=$C$1:C1)*($E$1:E1);0))}

Array formula after editing is confirmed by pressing ctrl + shift + enter

Replace semicolon with comma in formula if needed.

enter image description here