## 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 an 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 2007 formula in C18:**

Recommended article

Sum values in a range where adjacent cell value equals a criterion

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is […]

**Alternative excel 2003 formula in C19:**

Recommended post

I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]

**Alternative array formula:**

Press and hold CTRL + SHIFT simultaneously and then press Enter to create an array formula.

Recommended article

Sum only if unique value in another column in excel

Question: How do I sum values only if a unique value exists in another column? Answer: Formula in D13: =SUM(IF(COUNTIF(List1, […]

### Download excel file for this tutorial

Sum values between two dates with criteria.xls

(Excel 97-2003 Workbook *.xls)

Functions in formulas above

The SUM function in excel allows you to add values, the function returns the sum in the cell it is […]

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

**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 B9:**

**Download 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

### Recommended articles

Sum values in a range where adjacent cell value equals a criterion

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is […]Filter unique distinct list sorted based on sum of adjacent values [Array Formula]

Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]Excel udf: Find positive and negative amounts that net to zero

I created/modified an udf to solve his question: Find positive and negative amounts that net to zero in excel Question:I would […]### 27 Responses to “Sum values between two dates and based on a condition”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hello, I found this web site when i was searching Bling for websites related to this article. I must tell you, your site is good. I like the theme too, its pleasing. I don't have much time now to fully read your site but I have noted it and I also registered for your RSS feed. I will be back in a day or two. Thanks for an useful website.

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. Click "Evaluate Formula" button

3. Click 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.