Search code examples
excelexcel-formulaexport-to-excel

Adding Sum based on the month criteria

I am planning to add a simple formula to calculate the sum for each individual based on month (i.e. cell C14). If I change the month to March, then sum should be taken from Jan to Mar and if I change the month to December, then sum should be taken from Jan to Dec for that particular person.

I am currently using the follwoing formula, but it is coming too big. I have just added from Jan to Apr only and it is coming this long. I don't want such a big formula. Is it possible to change it to very simple formula?

=IF(C14="Jan",VLOOKUP(A17,$A$3:$M$11,2,0),IF(C14="Feb",VLOOKUP(A17,$A$3:$M$11,2,0)+VLOOKUP(A17,$A$3:$M$11,3,0),IF(C14="Mar",VLOOKUP(A17,$A$3:$M$11,2,0)+VLOOKUP(A17,$A$3:$M$11,3,0)+VLOOKUP(A17,$A$3:$M$11,4,0),IF(C14="Apr",VLOOKUP(A17,$A$3:$M$11,2,0)+VLOOKUP(A17,$A$3:$M$11,3,0)+VLOOKUP(A17,$A$3:$M$11,4,0)+VLOOKUP(A17,$A$3:$M$11,5,0)))))

enter image description here

Solution

  • Try this

    B17:  =SUM(OFFSET($A$2,MATCH(A17,$A$3:$A$11,0),1,1,MATCH($C$14,$B$2:$M$2,0)))

    and fill down as needed.

    I'm not sure if I can come up with a non-volatile formula.

    In the above, we use the MATCH function to compute both the row offset into the table; as well as the width of the returned array.