Calculating Monthly and Accumulated Income, Based on Specified Month

 

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)

 



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>