Problem:
Cells B3:M5 contain the monthly income from three different sources (listed in cells A3:A5).
Row 1 (cells B1:M1) contains the serial number matching each month in row 2 (cells B2:M2).
Row 6 (cells B6:M6) contains SUM formulas, calculating total monthly income.
We want to retrieve the amounts matching each source of income, as well as the total income, for a specified month.
We also want to calculate the accumulated income from each source, up until (and including) that month.
In addition, we want to change the titles above the formulas to include the month name.
Solution:
To retrieve income per month, use the SUMIF function as shown in the following formula in cell B9:
=SUMIF($B$1:$M$1,$J$8,B3:M3)
To calculate the accumulated income up until the month indicated, use the SUMIF function as shown in the following formula in cell C9:
=SUMIF($B$1:$M$1,"<="&$B$10,B3:M3)
To change the titles in cells B8:C8:
Use the INDEX function as shown in the following formulas:
To change the title in cell B8:
=INDEX(B2:M2,J8)&" "&"Income"
To change the title in cell C8:
="Accumulated Income Until" &" "& INDEX(B2:M2,J8)
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
