• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to sum a range between certain days of month?

S

Smitty15

New Member
I have a column which tracks the date of certain expenses and separate column with the expense. Each expense is entered separately, therefore there may be multiple rows with the same date. How can I write a formula to sum the expenses between a predefined date range? Currently, I am entering information as "10/01/2010" and expenses simply as a number.
 
Something along the lines of


=SUMIF(date_range,">=2010-10-01",value_range)-SUMIF(date_range,">=2010-11-01",value_range)
 
Thank you for the response. It worked great. However, looking further into this, how can I include an additional condition into the function that basically asks:


If A3:A80="D", and then do the same thing with the SUMIF function you provided.


Any idea how I can incorporate this into your function?
 
Have a lok at Sumifs instead of Sumif

Sumifs allows multiple conditions
 
Use


=SUMPRODUCT(--(A3:A80="D"),--(date_range>=--"2010-10-01"),--(date_range<--"2010-11-01"),value_range)
 
Try this:

=SUMPRODUCT(--(A3:A80="D"),--(date_range>="2010-10-01"),--(date_range<="2010-11-01"),value_range) + Enter


Adjust date and value ranges.
 
If you are using dates you can't use "2010-10-01" as that is text


Code: 
=SUMPRODUCT((A3:A80="D")*(date_range>=date(2010,10,1)*(date_range < Date(2010,11,1),(value_range))


This will sum up the values in Value_Range where 1/10/2010 <= Date < 1/11/2010
 
<<If you are using dates you can't use "2010-10-01" as that is text>>


No, but you can use --"2010-10-01" as I showed.
 
<<If entered exactly as above, this only returns a #VALUE! >>


Did you adjust date_range and value_range to your ranges, and size them the same as A3:A80?
 
Hui,


Yeah, I realised that, but I added it to try and make it clear after Oscar's post as his confused the issue I felt and you (seemed to) support him by offering a different solution.


I tried to clarify it, seems I failed :)
 
You must log in or register to reply here.
Back
Top