Excel 2008 SUMPRODUCT Filter by Date Question


Hoping someone could assist.

Sheet1:  Column D4 has dates (10/01/2013. 10/03/2013, etc.)
Sheet 2:  Column C15 has a data validation list showing every month in a year.
Sheet 2:  Column C19 has the sumproduct formula.  All the data is being pulled from Sheet1 in this formula.

How can I adjust this formula:
=SUMPRODUCT((Position_Applied_For = $B19)*(Language = $C$14)*1)

...so that based on the selection in the validation list it only pulls data for the month of October in sheet 1?

Who is Participating?
byundtConnect With a Mentor Commented:
I created a dynamic named range Application_Date referring to column D, then used this formula:
=SUMPRODUCT((Position_Applied_For = $C9)*(Language = $D$4)*(TEXT(Application_Date,"mmmm")=$D$5))
In the SUMPRODUCT formula you posted, you don't need the part with *1 because you are already multiplying two Boolean expressions.

The following formula assumes that you want dates for the current year. If you want to consider previous years, you'll need to build the year into the concatenations for the beginning of the month.

The formula also assumes that your named ranges run from row 2 through row 1000. If not, please adjust (or used a named range) date column D part of formula.
=SUMPRODUCT((Position_Applied_For = $B19)*(Language = $C$14)*(Sheet1!$D$2:$D$1000>=--($C$15 & " 1"))*(Sheet1!$D$2:$D$1000<=EOMONTH(--($C$15 & " 1"),0)))
barry houdiniCommented:
Does the validation list show months as text values like January, February etc. or are they actual dates (first of each month)?

If it's the former try this version

=SUMPRODUCT((Position_Applied_For = $B19)*(Language = $C$14)*(TEXT(Dates,"mmmm")=C15))

or for the latter try COUNTIFS like this


Where Dates represents your range of dates

regards, barry
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

You could also use the MONTH function for a shorter formula. It will be slower, however, because the formula needs to calculate the month for each cell in the date range.
=SUMPRODUCT((Position_Applied_For = $B19)*(Language = $C$14)*(TEXT(Sheet1!$D$2:$D$1000,"mmmm")=$C$15))

If these formulas are not working for you, please post a sample workbook that demonstrates the problem.
EscanabaAuthor Commented:
Thank you for quickly responding. I've tried this options and cannot seem to get it to work.  I've attached a sample file which I should of done in the first place.
This type of problem can also be solved using a PivotTable. The advantage of PivotTables is that no formulas are required. I've set one up for you on Sheet2 in the attached workbook.
EscanabaAuthor Commented:
Perfect!  I tried the pivot table option but I needed to show the detailed breakdown on other areas of the spreadsheet.  Otherwise that would be an a good route to go on.  Thanks again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.