Counting entries between dates using sumproduct?

[Simon Lloyd]

Hi all, i have an issue with a SUMPRODUCT formula i'm using, i use the formula to count instances of data validated text within a named range (multiple columns), the forumla works perfect, i even use it to check two data validated cells for dates and count between the dates, the problem i have now is that i need to be able to count instances of text as once per 7 day date range for each column, below is the formula and attached is my sample workbook.

=C$2-SUMPRODUCT(((INDIRECT("'"&$B3&"'!cnproc")="TRN")+(INDIRECT("'"&$B3&"'!cnproc")="SIC")+(INDIRECT("'"&$B3&"'!cnproc")="LD")+(INDIRECT("'"&$B3&"'!cnproc")="SAF")+(INDIRECT("'"&$B3&"'!cnproc")="UN")+(INDIRECT("'"&$B3&"'!cnproc")="ABO"))*(dlist>=$A$3)*(dlist<=$A$5))

[Simon Lloyd]

I've been working on this and trying to incorporate network days in the forumula with no success, the weeks run from Sunday to Saturday (inclusive), i think i may have to resort to VBA after all although i'm now looking at a helper column and trying to split the selected period up - will post back any formula that i get even half way there :)

[Bob Phillips]

Simon,

I would suggest a bit of VBA that creates a helper SHEET, namely one that creates a weekly view of the full data with a 1 or 0 if that week contains any of the codes, and then a simple formula to add up the values.

[Simon Lloyd]

Thats what i have been creating Bob, try as i might i'm not clever enough to create a formula to work with that. The solution i am working on isn't a worksheet though, i'm simply checking if a named range has entries between the two dates if yes then check columns for entries in that range and count the number of instances for 7 days and add 1 to a total, check the next 7 days...etc and populate a second grid so they get to see the actual amount of absences and their availability view.

I've tried to keep the VBA in this workbook down, i had to have code for the data validation as we needed that functionality but everything else i wanted with native worksheet functions

[Bob Phillips]

I am not sure if we are at cross-purposes, but I am saying create a helper sheet with all of the data summarised over sheets, so it would have dates of 30/10, 6/11, 13/11, etc, and the columns would not show the codes, just 1 if any codes are present, 0 if not. The formula is then trivial.

[Simon Lloyd]

I understand Bob, but for each column that had multiple codes for each 7 day period in the selected date range would have to count as one, so if there were 4 entries for the week beginning 30/10 then that would only count as 1 just for that 1 column, so the next column in the named range, if it had entries within each 7 day period of the selected dates would add to the total as 1 for each period, so given the period below:
30/10/11
12/11/11
if there were 4 instances in the first 7 day period and 2 in the second then the coun t would be 1, move to the next column in the named range and do the same for the period.

So thje way i see it, it's probably easier to just do the calculation in VBA for each column in each named range then populate a grid with the results.

Am i missing a trick?