The problem here is summing sales figure from the start of the year to a particular month
Sales of 2 products (Item1 and Item2) is given for an entire year – Jan to Dec. The objective is to find cummulative sales till a particular month. For instance the input is Item1 and Jun – where the output should be sum of sales of Item1 from Jan to Jun (highlighted in yellow).
The desired result is given in the table above. I have used offset function to obtain the result. The function is used with multiple width.
The formula used in C6 is =SUM(OFFSET($A$1,MATCH(B6,$A$2:$A$3,0),1,1,MATCH(A6,$B$1:$M$1,0)))
The first match function returns 1, which is number of rows to offset from the reference A1, the second match function returns 6 – position of month Jun in Jan to Dec – which equals the width of column to sum i.e. Jan to Jun.
Note that this is entered as a simple formula and not an array formula.
The formula can be dragged down to obtain result in C7 – which is summation of sales of Item2 for YTD Mar.
Thanks Bret for highlighting this problem.
Have fun 🙂
Leave a comment