Count Multiple Criteria within Multiple Date Ranges

Hello All,

I'm attempting to create a formula that continuously adds itself together for the same months as the years go on. Each formula is looking for the same two criteria, just for the same month of different years; so essentially I do not have to make a chart for each different year, it continuously adds to the same total number for that same month as the years go on.

Here is my current formula (that doesn't work):

=SUMPRODUCT((SATC!$D$6:$D$500=B46)*(SATC!$A$6:$A$500=1)*(SATC!$H$6:$H$500>=Reports!$G$3)*(SATC!$H$6:$H$500<=Reports!$H$3)*OR(SATC!$H$6:$H$500>=Reports!$G$4)*(SATC!$H$6:$H$500<=Reports!$H$4))

Formula Breakdown:

• (SATC!$D$6:$D$500=B46) // B46 is simply a condition to be met.

• (SATC!$A$6:$A$500=1) // Another condition to be met, and this column consists of '1's and 0's, and I only want to return the '1's.

• (SATC!$H$6:$H$500>=Reports!$G$3)*(SATC!$H$6:$H$500<=Reports!$H$3)*OR(SATC!$H$6:$H$500>=Reports!$G$4)*(SATC!$H$6:$H$500<=Reports!$H$4)) // The rest are all date ranges by month (e.g. >=Reports G3 is June 1, 2009, <=Reports H3 is June 30, 2009; while the next set of date ranges are for the same date period just in 2010, 2011, 2012, etc...)

I know the date comparison side of the formula is where the issue lies, as if I leave the first date range in there than the formula works and is correct; I am simply not sure how to examine multiple date ranges, and if there is data within the range, to add them all together; rather than finding a false (0) statement and multiplying it to the rest to come up with a 0. I also tried a =COUNTIFS formula and came up with the same issues.

It seems such a simple task but I can't figure it out!

Any help is appreciated!

First up - I tweaked your title slightly to make more specific (for sake of search facility)

OR's in Arrays/SUMPRODUCT are conducted (generally) by means of addition, ANDs by Multiplication.

Please Login or Register  to view this content.

if you need more assistance best to post a sample file.

edit: should add that in the above we assume no date ranges overlap (if they do you need to account for possibility of double counting by testing resulting Sign of OR)

If your range was always one month.. like JUNE, JULY, etc, then:

Please Login or Register  to view this content.

but if not, then you would need something more complicated like:

Please Login or Register  to view this content.

where G3:G5 and H3:H5 hold the date ranges...

confirmed with CTRL+SHIFT+ENTER not just ENTER.