Solved

Sumif excel with dates

Posted on 2011-03-25
14
242 Views
Last Modified: 2012-05-11
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
Comment
Question by:jheg
  • 4
  • 4
  • 4
  • +2
14 Comments
 
LVL 33

Expert Comment

by:jppinto
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

by:Saqib Husain, Syed
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

by:jheg
ID: 35218887
I was hoping not add another column to the sheet.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 33

Expert Comment

by:jppinto
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

by:Saqib Husain, Syed
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

by:jheg
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

by:barry houdini
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

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

jppinto
Book1.xlsx
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 200 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

by:jheg
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

by:
jppinto earned 300 total points
ID: 35219200
Here you go...

jppinto
Book1.xlsx
0
 

Author Closing Comment

by:jheg
ID: 35219242
Thanks, exactly what I was look for.
0
 
LVL 6

Expert Comment

by:reitzen
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

by:Saqib Husain, Syed
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question