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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
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
byundtMechanical EngineerCommented:
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.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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.
byundtMechanical EngineerCommented:
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))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.