Thread: SUMPRODUCT using 2 different date ranges

Good afternoon!

I'm having quite a bit of trouble with an excel formula and I've been killing myself trying to figure it out all morning.

I'm trying to create a SUM formula with a lot of criteria. I can get everything to work, but there is one thing that messes it up. I want to add an "OR" into the SUMPRODUCT formula I'm using. I want the cell to search and sum when a row is between two dates, have a "1" in Discipline Level, and go to a specific school. Here's the catch, I have 2 different schools, "1" & "3", both using different date ranges. No student has both "1" & "3" show up as a school ID. It's either one or the other.

Date Range for school 1: 9/15/08 - 11/16/09
Date Range for school 3: 9/02/08 - 11/08/09

What I'm trying to do is count whenever discipline level "1" appears but only count between the date ranges for their school. Below is the formula I'm using and the info for each column.

E = Date
D = Discipline Level ("1")
K = "School ID"

=SUMPRODUCT(--('Intervention Summary'!$E$2:$E$200>=DATE(2008,9,15)),--('Intervention Summary'!$E$2:$E$200<=DATE(2008,11,16)),--('Intervention Summary'!$D$2:$D$200=1),--('Intervention Summary'!$K$2:$K$200=1),--('Intervention Summary'!$D$2:$D$200))+(SUMPRODUCT(('Intervention Summary'!$E$2:$E$200<=DATE(2008,9,2)),--('Intervention Summary'!$E$2:$E$200<=DATE(2008,11,8)),--('Intervention Summary'!$D$2:$D$200=1),--('Intervention Summary'!$K$2:$K$200=3),--('Intervention Summary'!$D$2:$D$200)))

I don't get an error when I enter this formula, but I always end up getting "0" results.

Any help would be GREATLY appreciated. Thank you!

They are all numeric. D= 1-9 and K= 1-4 only. There are occasional blanks cells too.

I tried Text To Columns for both and nothing happened.

Here it is. I've highlight the section I'm working on in the "Cover Page" tab.

You my friend, are awesome! It worked perfectly!

You just saved me tons and tons of grief. Thank you so much!