## Sum values between two dates with criteria in excel

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

**Alternative excel 2003 formula in C19:**

**Alternative array formula:**

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

### Download excel file for this tutorial

Sum values between two dates with criteria.xls

(Excel 97-2003 Workbook *.xls)

### Sum formula examples

**Sum values where adjacent cell value equals a criterion**

This post demonstrates how to sum adjacent values using a condition.

**Sum only if unique value in adjacent column**

This post explains how to construct a formula that adds values if adjacent value is unique.

**Excel SUM function**

Explains how the SUM function works with many examples

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

### Category: Sum

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]

Comments(48) Filed in category: Combinations, Excel, Solver, Sum

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

Comments(19) Filed in category: Excel, Sum

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Comments(13) Filed in category: Excel, Mmult, Permutations, Sum

Introduction What is unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

Comments(8) Filed in category: Count values, Dates, Excel, Frequency, Sum, Unique distinct values, Year

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

Comments(7) Filed in category: Count values, Excel, Mmult, Sum

I created/modified an udf to solve his question: Find positive and negative amounts that net to zero in excel Question:I would […]

Comments(5) Filed in category: Excel, Sum

This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]

Comments(2) Filed in category: Excel, Finance, Sum

This article explains how to build an array formula that sums ranges. Example, I want to know how to calculate […]

Comments(2) Filed in category: Excel, Sum

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

Comments(2) Filed in category: Check-boxes, Excel, Sum

### 27 Responses to “Sum values between two dates with criteria in excel”

### 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.