Microsoft Excel's
Sum function allows you to sum the values
in a column. E.g., if I have a column, column J, that contains revenue in
dollars for items sold with the first entry in row 4 and the last entry in
row 62, I could use the formula =SUM(J4:J63) to calculate the
total revenue for all items (I use "63" instead of "62" presuming that new
entries will continue to be added by inserting new rows above row 63). But,
if you wanted to determine the revenue just for the current month, presuming
the month is April 2012, you could use the sumif function, e.g.,
assuming the date of sale for each item is in column F, I could use the
formula =SUMIF(F4:F63,">=2012-04-01",J4:J63) to calculate the
revenue for any items sold on or after April 1, 2012, presuming that I am
still in the month of April 2012. In this case any row that has a date
value of April 1, 2012 or later in column F will have the sales amount in
column J for that entry added to the total sales calculation for April.
I could also have used "01-Apr-12" for the date value in the formula, if
I wished, instead of "2012-04-01", i.e., I could use YYYY-MM-DD where YYYY
is the year, MM is the two digit value for the month and DD is the two digit
value for the day or DD-Mon-YY, where Mon is the 3 letter abbreviation for
the month.
Presuming that the only entries on the worksheet are for the year 2012,
I could find the total sales for January using the formula
=SUMIF(F4:F63,"<=31-Jan-12",J4:J63). But, if I want to
determine the sales for February, I need to add only entries where the
date is between February 1, 2012 and February 29, 2012. One simple way to
do so that will work even for older versions of Excel, such as
Microsoft® Excel 2000, is to perform one SUMIF calculation
for all the values greater than or equal the starting date and then subtract
from that total the value calculated using another SUMIF formula
that adds all the values where the date is greater than the ending date. E.g.,
if I wanted to determine the total revenue for February, I could use the forumla
below:
=SUMIF(F4:F63,">=2012-02-01",J4:J63)-SUMIF(F4:F63,">2012-02-29",J4:J63)
The first SUMIF adds the sales amount in column J whenever the
date value in column F is greater than or equal February 1, 2012. The next
SUMIF adds the sales amount in column J whenever the date
value stored in column F is greater than the last day in Feburary of 2012,
which is February 29, 2012. The result of the first SUMIF will
be the total for all entries on or after February 1, 2012. The result of
the second SUMIF will be the total for all entries after February
29, 2012. The difference between those two values will be the total for all
sales in the month of February 2012.
