1. ## Add Date Range to SUMPRODUCT Formula

I trying to modify an existing formula, but I’ve found it to be harder than I anticipated. I hope someone on this forum can help me. The formula is:

=SUMPRODUCT(\$E\$2:\$E\$11,H4,--(\$C\$2:\$C11="RED"),--(\$D\$2:\$D\$11="CCA"))

I would like to add additional criteria for a dates range between Jan 1 and Jun 30 of the current year (Column B). So the formula would total the Points if the Dates are between 01/01 (current year) and 06/30 (current year) and the Team = “RED” and the Area = “CCA”.

Excel.jpg

Thank you.

2. Hi

Welcome to the Lounge.

..have you thought about using the SUMIFS formula??

zeddy
•Excel Protocol Engineer
.

3. ## The Following User Says Thank You to zeddy For This Useful Post:

JAATR (2015-08-03)

4. zeddy,

Thank you for the suggestions; I used the following formula and got the results I was looking for:

=SUMIFS(\$E2:\$E\$11,\$B\$2:\$B\$11,">=1/1/15",\$B\$2:\$B\$11,"<=06/30/2015",\$C\$2:\$C\$11,"=RED", \$D\$2:\$D\$11,"CCA")

One other questions concerning the date range (year). I will reuse the worksheet each year, so I will need to adjust/update the formula according (e.g., replace 2015 with 2016 next year). Is it possible to substitute something for 2015 so that I don't have to modify the formula every year?

5. Jaatr,

Just place the start end dates in cells, name the cells, then use the names in the formula.
Example1.JPG

An alternative is to create Names that contain the dates then you just change the Name definitions each year, and as previous use the names in the formula.
Example2.JPG

Names in Workbook:
Example3.JPG

Note: BegPeriod and EndPeriod were entered as 1/1/2015 & 6/30/2015 respectively in the name definition box and converted by Excel to their respective date numbers.

Test File: NamedValues.xlsx

HTH

6. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

JAATR (2015-08-03)

7. Try:
=SUMPRODUCT((\$E\$2:\$E\$11)*((\$C\$2:\$C11="RED")*(\$D\$2: \$D\$11="CCA")*(B2:B11>DATE(2015,1,1))*(B2:B11<DATE( 2015,6,1))))

Maud

8. Modified so the year will allways be the current year without having to change it:

=SUMPRODUCT((\$E\$2:\$E\$11)*((\$C\$2:\$C11="RED")*(\$D\$2: \$D\$11="CCA")*(B2:B11>DATE(YEAR(TODAY()),1,1))*(B2: B11<DATE(YEAR(TODAY()),6,1))))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•