Add Date Range to SUMPRODUCT Formula

Topic

New Reply

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

41554-Excel

Thank you.

Reply | Quote

Replies

Hi

Welcome to the Lounge.

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

zeddy
•Excel Protocol Engineer
.

Reply | Quote

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?

Reply | Quote

Jaatr,

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

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.
41557-Example2

Names in Workbook:
41558-Example3

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: 41559-NamedValues

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote