x
Solved

# Sumif excel with dates

Posted on 2011-03-25
Medium Priority
258 Views
I have a worksheet that contains dates (01/25/2010) in column A and costs in column B. I am trying to create a sumif formula =sumif(\$A\$2:\$A\$325,criteria,\$B\$2:\$B\$B325. I am looking what expression to use to find if date is with a certain month and year. I would like to add up all costs for rows falling with the month for each month...Jan, Feb, Mar..
0
Question by:jheg
• 4
• 4
• 4
• +2

LVL 33

Expert Comment

ID: 35218848
You could put a 3rd column for month/year and then make a Pivot Table to get the values for each month/year. It would be the simplest way of doing this, in my opinion, or you would have to build a table with each combination of month/year and put a formula to get the values.

Please take a look at the attached example to see what I'm saying.

jppinto
Book1.xlsx
0

LVL 43

Expert Comment

ID: 35218873
If you put a date in I2 then this will give the sum for the month in I2.

=SUMPRODUCT((MONTH(D3:D16)=MONTH(\$I\$2))*(YEAR(D3:D16)=YEAR(\$I\$2))*H3:H16)

Saqib
0

Author Comment

ID: 35218887
I was hoping not add another column to the sheet.
0

LVL 33

Expert Comment

ID: 35218900
The problem in using a formula like the one provided on the previous post, is that you need to put on a range of cells something like this:

Jan-10   Feb-10   Mar-10 .... until Feb-11

and everytime you have another month you need to put more month/year and copy the formula.

jppinto
0

LVL 43

Expert Comment

ID: 35218904
For my formula if you do not want to use a cell like I2 then simply replace both the \$I\$2 in the formula with a valid date which gives the desired month.

Saqib
0

Author Comment

ID: 35219093
ippinto: I do understand about having to copy the formula, but we are only doing 24months.

Saquib, tried your formula and replaced I with month an year, but do not want to add up the month but add the cost in the cell next to it.
0

LVL 50

Expert Comment

ID: 35219111
Which version of excel are you using?

In Excel 2007 and later versions you can use SUMIFS

=SUMIFS(\$B\$2:\$B\$325,\$A\$2:\$A\$325,">="&D2,\$A\$2:\$A\$325,"<="&EOMONTH(D2,0))

where D2 contains 1st of the month

You can list all the months in D2 down then just copy the formula down the adjacent column

regards, barry
0

LVL 33

Expert Comment

ID: 35219145
Here's another working example with a SUMPRODUCT() formula.

jppinto
Book1.xlsx
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 800 total points
ID: 35219154
Sorry I used the references for my worksheet

try

=SUMPRODUCT((MONTH(A3:A16)=MONTH(\$I\$2))*(YEAR(A3:A16)=YEAR(\$I\$2))*B3:B16)

And make sure you replace the \$I\$2 with a complete date in quotes

like

=SUMPRODUCT((MONTH(A3:A16)=MONTH("2/4/2011"))*(YEAR(A3:A16)=YEAR("2/4/2011"))*B3:B16)
0

Author Comment

ID: 35219191
this is what I am trying accomplish

Date- W/E        Driver cost                   Month      Driver Cost
Jan-10       \$102,343.90 (need this formula)
1/8/2010               \$30,174.30                  Feb-10       \$100,622.75
1/15/2010       \$26,782.65
1/22/2010       \$21,292.20
1/29/2010       \$24,094.75
2/5/2010               \$30,389.60
2/12/2010       \$25,704.00
2/19/2010       \$21,973.10
2/26/2010       \$22,556.05
0

LVL 33

Accepted Solution

jppinto earned 1200 total points
ID: 35219200
Here you go...

jppinto
Book1.xlsx
0

Author Closing Comment

ID: 35219242
Thanks, exactly what I was look for.
0

LVL 6

Expert Comment

ID: 35219275
If the number of rows is going to grow, then you'll need a more dynamic solution:

=SUMPRODUCT((MONTH(\$A:\$A)=MONTH(D2))*(YEAR(\$A:\$A)=YEAR(D2))*(\$B:\$B))

Column A = Date-W/E
Column B = Driver cost
Column D = Summary Month/Year
Column E = Formula

This will capture all rows.
0

LVL 43

Expert Comment

ID: 35219301
That was neat jppinto!

You use my formula in your file and take away most of the points. You should at least have acknowledged using my formula.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.