Author: Oscar Cronquist Article last updated on December 07, 2018

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:

=SUMIFS(D3:D10, C3:C10, "="&C15, B3:B10, "<="&C14, B3:B10, ">="&C13) + ENTER

The SUMIFS function adds numbers based on a condition or criteria and returns a total.

SUMIFS(sum_range, criteria_range1criteria1[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:

=SUMPRODUCT(--(B3:B10<=C14), --(B3:B10>=C13), --(C3:C10=C15), D3:D10) + ENTER

The SUMIFS function was introduced in Excel 2007, the SUMPRODUCT function works in all Excel versions.

Recommended post

Sum cells with check boxes

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

Sum cells with check boxes

Sum number based on corresponding unique value

The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]

Sum number based on corresponding unique value

Download excel file for this tutorial

Sum values between two dates with criteria.xls
(Excel 97-2003 Workbook *.xls)

Functions in formulas above

How to use the SUM function

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

How to use the SUM function

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

How to use the SUMPRODUCT function

Farhan asks:

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.

Formula in cell C10:

=SUMPRODUCT((($C$7<=$C$2:$E$2)*($C$8>=$C$2:$E$2))*($B$3:$B$5=$C$9)*$C$3:$E$5)

Download excel *.xlsx file

Farhan.xlsx

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