Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used Products

April
Sun Mon Tue Wed Thu Fri Sat
22

2012
Months
Apr

### Sun, Apr 22, 2012 4:01 pm

Sumif for Date Range

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.

Shop Amazon Local - Subscribe to Deals in Your Neighborhood