Sum values between two dates and based on a condition
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two date criteria and an additional criterion in an adjacent column (Product).
I have colored the cells in column Qty that meet all criteria.
Excel formula in C18:
The SUMIFS function adds numbers based on a condition or criteria and returns a total.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
The sum_range contains the numbers to be added: D3:D10
criteria_range1 (C3:C10) is the cell range that the criteria1 ("="&C15) will be applied to.
criteria_range2 (B3:B10) is the cell range (dates) that the criteria2 ("<="&C14) will be applied to.
criteria_range3 (B3:B10) is the cell range (dates) that the criteria3 (">="&C13) will be applied to.
SUMIFS(D3:D10, C3:C10, "="&C15, B3:B10, "<="&C14, B3:B10, ">="&C13)
Alternative formula in C19:
The SUMIFS function was introduced in Excel 2007, the SUMPRODUCT function works in all Excel versions.
Recommended post
Recommended articles
I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]
Recommended articles
The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]
Get excel file for this tutorial
Sum values between two dates with criteria.xls
(Excel 97-2003 Workbook *.xls)
Functions in formulas above
Recommended articles
The SUM function in Excel allows you to add values, the function returns the sum in the cell it is […]
Recommended articles
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
Recommended articles
Farhan asks:
2254 10 20 30
2253 10 20 10
2254 10 0 51
Criteria: required 2254 sum of values b/w my specified date let say from 6-8 Nov.
Formula in cell C10:
Get excel *.xlsx file
Functions in this article:
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
SUMIFS(sum_range, criteria_range1, criteria1,..)
Adds the cells specified by a given set of conditions or criteria
Sum category
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]
This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]
Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
To extract groups from cell range B3:B10 I use the following regular formula in cell B13.
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]
The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]
I will in this article demonstrate different ways to sum values, the first method is so easy and fast it's […]
This article explains why your formula is not working properly, there are usually four different things that can go wrong. […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
32 Responses to “Sum values between two dates and based on a condition”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thanks!
Thanks you saved alot of time for me
THanks buddy.... grt work.....
am i right in thinking that there shouldn't be a semi-colon after C3:C10. I removed it and put a comma instead and it worked
=SUMIFS(D3:D10, C3:C10;"="&C15, B3:B10, "="&C13) + ENTER
huw bevan,
Thanks for letting me know!
Site 6-Nov-12 7-Nov-12 8-Nov-12
2254 10 20 30
2253 10 20 10
2254 10 0 51
Criteria: required 2254 sum of values b/w my specified date let say from 6-8 Nov.
Farhan,
I added your question and the answer to this post.
Thanks for commenting!
Thank you so much for posting this. I had been wrestling with this issue for many, many hours, and your solution and explanation were by far the clearest and best I've seen. Thanks for sharing the knowledge!
Thank ..... It was really useful
Can you post an excel sheet containing top 50 of your solution??
Thank's
How to use it with filter date?
Good Day
Thanks so much for this information. I was working on this problem for about 4 hours before doing a search and finding this. It solved my problem immediately and perfectly !!!
JDC,
Thank you!
thanks you so much n thanks for your sharing
Thanks. You are the only one I have found that puts the cell-headers on their photos and even provides the example file! This fixed my headaches in less than 10 minutes for 3 of these calculations! Thanks again!
how can sum value with date criteria on another worksheet?
kisembo,
Original formula:
Change cell references!
Hi;
I google a lot, I found your website here. finally I got solution with simple explanation from you for my problem sum-total between two dates with category.
and much more to be explore from your site about excel it very helpful. thanks.
Yudi,
thank you!
The formula works for one range, but when applying to another date range, nothing is returned.
I have created new cells for other ranges and inserted them into the formula, but nothing is returned.
What do you suggest?
Ryan Chatt
What happens when you evaluate the formula?
1. Go to tab "Formulas"
2. Press with left mouse button on "Evaluate Formula" button
3. Press with left mouse button on Evaluate button repeatedly to see where the error is.
what if I need to reference another sheet (for the transaction value) AND use the SUMPRODUCT formula ?
How would I do that ??
Thanks!
Many thanks just what I have been searching for :-)
I get a #VALUE! error when applying the formula...evaluating the error shows that the dates are not recognized, coming up with #NAME? related to values in H22 and I22.
=SUMIFS('INVENTORY RECEIVED'!$P$8:$NP$228,'INVENTORY RECEIVED'!$D$8:$D$228,"="&C25,'INVENTORY RECEIVED'!$P$7:$NP$7,”=”&$H$22)
C25 (Sheet 1) = cell with product name trying to get the sum for between date range listed
I22 (Sheet 1) = Date Range End (31-Mar-2017)
H22 (Sheet 1) = Date Range Begin (01-Jan-2017)
P7:NP7 (Inventory Received) = row & columns with daily dates (Jan 1 - Dec 31)
P8:NP228 (Inventory Received) = cell range with values to sum
D8:D228 (Inventory Received) = column with product names to lookup
So, in the 'Master File' (Sheet 1), I want to find out how many items were purchased for product named in cell C25 between dates I22 and H22, listed in the table on sheet 'Inventory Received' within the data range P8:NP228...
Thanks!
Sorry, this is the formula:
=SUMIFS('INVENTORY RECEIVED'!$P$8:$NP$228,'INVENTORY RECEIVED'!$D$8:$D$228,"="&C25,'INVENTORY RECEIVED'!$P$7:$NP$7,”=”&$H$22)
Hi Oscar,
Not sure what is happening and why the formula keeps getting truncated when pasting...Writing the formula in its individual parts, hopefully this works...
=SUMIFS('INVENTORY RECEIVED'$P$8:$NP$228,
'INVENTORY RECEIVED'!$D$8:$D$228,
"="&C25,
'INVENTORY RECEIVED'!$P$7:$NP$7,
"="&$H$22)
In the Excel formula evaluator, the $I$22 and $H$22 return #NAME? errors, though the $P$7:$NP$7 range returns the dates listed (in 42736 format)...Thanks again.
That didn't work either...never mind.
Amazing Farhan thank you. your formula support for my excel file workings
Thank you so much for this!! I could not find this anywhere online. We use a Google Doc Form where employees submit their PTO requests using the values:
- Name/Email
- PTO Start Date (all years)
- Number of weekdays they will be off
I used your formula to total the number of days taken per year per employee and worked perfectly!
Screenshot: https://postimg.cc/LY5dt85j
So awesome! Thank you!
Hello excel gurus,
Based on table 1, is there a way to calculate the number of tasks a given resource (who) is assigned and working for each calendar day (table 2). I've given a tried using sumproduct or countifs but I haven't found the way to get the desirable results.
I will appreciate any insight about it.
Thanks!
table 1:
task_name who start_dt end_dt
task1 CR 1/8/2021 1/8/2021
task2 MS 1/8/2021 1/9/2021
task3 CR 1/8/2021 1/11/2021
task4 CR 1/13/2021 1/15/2021
table 2:
1/7/2021 1/8/2021 1/9/2021 1/10/2021 1/11/2021 1/12/2021 1/13/2021 1/14/2021 1/15/2021
CR 0 2 1 1 1 0 1 1 1
MS 0 1 1 0 0 0 0 0 0
I just figured out what was the issue. Using sumproduct formula, I had forgotten to include ctrl +shift + enter at the end.
e.g.
=SUMPRODUCT(($K$2:$K$5=O$1)*($J$2:$J$5=$N2)) then CTRL+SHIFT+ENTER
Thanks anyway!
I want to thank you SO MUCH for this! You have no idea how much you helped me!
ANA MARIA
You are welcome!