Counting number of records in a given date range.

Looks like Dynamic Named Ranges and SUMPRODUCT() will be the way to go with 2003.

Can you post a dummy workbook?
Remember to disguise any potentially sensitive data.

Hi Sinnie

Yes, Marcol probably just needs to see how your data is set out.

Okay, the first problem is that Column A doesn't have a continuous list of dates.

I have added Column J, it can be retained as a helper column, or to Copy > Paste Special > Values to Column A.

The second problem is that your sample row of results appears to be wrong.

I have put a formula in C2 that can be dragged across to G2, then down.
This is based on Column J for now.
B2 sums C2:G2

I have filtered Column J for the Dates 01/01/2013-07/01/2013 to allow you to check my result.

Check this over, I have to go for now, I'll look back later to get your response, and possibly take it forward, if required.

Try this workbook.

I have added Dynamic Names to Sheet "Raw Data"

I have moved the helper column "Infill Dates" to Column A, this can be hidden with the +/- grouping button.
This allows you to add more "Behaviour catagory" headers to row 1 without changing the Dynamic Names Formulae.

When you add a date to Column B, or a time to Column C, Column A will automatically update, and the Named ranges will adjust to suit the last entry.

To extend the table depth, select the last row and drag down as required.
To extend the table width, just add a new catagory header.

In Sheet "Weekly Frq" enter your first Date in A2, the remaining dates will update in weekly increments.

If you add new catagories to "Raw Data", add exactly the same header to this sheet, then drag the formula in G2 across and down.
The headers in this Sheet from Column C right need not be in the same order as those in Sheet "Raw Data" Column D right.

I've been able to download at work, which is good.

Anyway, I've had a good play around with everything and got it all to work, except I realise an omission on my part has caused a slight problem. The raw data worksheet, with the types of behaviour columns will actually be used to record the severity of the behaviour (1-4 for example). If the same behaviour occurs twice in one day then it is entered as a seperate incident on a new line. in the weekly frq's worksheet it currently sums the entries into the behaviour cells in Raw Data. I tried swapping SUMPRODUCT for COUNT but it just returned 0 as a result. If it isn't possible to count the number of entries then I can just include the severity elsewhere.

The spreadsheet is now operating significantly slower, is this a result of it now calculating across multiple cells and worksheets? I have extended it down to 500 cells as there is a lot of back data to be added. Will this only get worse if I add graphs?

If this file starts to slow down is it possible to create a second excel file that pulls the information from this one to make the graphs which would then work faster?

Is there any way to make this system work but on a monthly basis? Currently it works on taking the previous date and adding 7 for the week, but obviously this will not work for months due to different lengths.

Last question, is it possible to copy the formula automatically from the infill dates to a newly created row. So if I insert a line between two rows that are already filled in then the formula is created instantly in infill dates.

Other than that it appears to work perfectly, thanks very much!

Let's take this a step at a time!

See if this Counts the instances of monthly incidents, as you need.

Sheet "Raw Data" now has an Excel List instead of a table.
To extend the table, Select any cell within the List, then drag the bottom right corner down, or add data to the last row beginning with Column B, the formula in Column A will be automatically generated.
Similarly, if you insert a row the formulae in Column A will automatically adjust.

With Sheet "Monthly Frq" enter your start date in A2 as for example if you want the results to start with February, enter 1/2/2013,
EOMONTH() calculates the number of days to increment/count.

Originally Posted by Sinnie

...
The excel list works perfectly, thanks.
As does the auto month fill in, however the cells all list #NAME? and I have found multiple potential reasons for this and can't make heads or tails of it.
....
All i need to do is change it to count the number of entries instead of summing then it should be ready to go.
....

Using an Excel List will slow the calculation, but if you want to insert rows mid table without manually updating the formulae, then this is the way to go. It might be faster to use VBa to do this but let's not go that way yet.

To Sum the severity of the weekly incidents use this in C2

Formula:

=SUMPRODUCT((InFill_Dates>=$A2)*(InFill_Dates<=$A2+6)*(INDEX(Data_Table,,MATCH(C$1,Catagories,0))))

To Count the weekly incidents use this

Formula:

=SUMPRODUCT((InFill_Dates>=$A2)*(InFill_Dates<=$A2+6)*ISNUMBER((INDEX(Data_Table,,MATCH(C$1,Catagories,0)))))

Where are you getting #NAME? errors, I can't see any in this workbook.

Originally Posted by Sinnie

The #NAME? error is in the monthly worksheet you uploaded in copy of example(3).

I am not getting any #NAME? errors. Have you changed, or added, something?

I've just downloaded "Copy of Example(3)" from post #11 into Excel 2003, and I can't see any #NAME? errors ...

What version are you using?

Microsoft Office Excel 2003, same version as I've been using throughout. Re-downloaded from post #11, "Copy of Example(3)" and the monthly worksheet is full of #NAME? errors.

Ok, I discovered you can 'trace error' and this is what I have:
Tracing the error in B2 - blue line from A2 to B2 turning into red line and continuing into C2 then goes diagonally as a dashed blue line to the corner of cells A3/4 and B3/4 - there is also a blue line from C1 down to where the red and blue line meet in C2. Hope that clarifies something!

I can also evaluate the error but im not really sure how it works.

That opens without error for me, it was however a tad slow in opening, and the "errors" were briefly displayed before clearing.
I can only assume that your copy of Excel is not refreshing properly.

Are you using the last version of MS office 2003 with service pack 3 (SP3) installed?

I'm going to ask others to see if they can replicate your error and shed some light on the problem.

No errors for me, Marcol.

#NAME error probably due to EOMONTH formula in Column C:G? i dont think it exist in Excel 2003 unless you install the add-in. an alternative may be:
DATE(YEAR(A2),MONTH(A2)+1,0)
replacing
EOMONTH($A2,0)

EOMONTH is an Analysis ToolPak function in Excel 2003 - if you don't have that add-in you will get #NAME? error - try installing by using

Tools > add-ins > tick Analysis ToolPak box

....or you can workaround with

=DATE(YEAR(A1),MONTH(A1)+1,0)

Edit: as benishiryo says........

Me too! .............

I don't see any error

Wonderful! That solved it. Thanks very much.

I believe this solves the whole case now, with much help from Marcol.

You have my eternal gratitude.

Edit: And yet I speak too soon, hopefully it's just a minor one. I realised that I also wanted to calculate the total number of 'incidents' per week/month which is sepeate from total behaviours as multiple behaviours can occur within one incident. The best way I saw to do this was to create a new column in the weekly/monthly worksheet called Incident Number and to copy the formula used on the other columns to obtain the total of incident numbers within each week or month. But it always comes back as 0. All the other columns work and I have added several new ones for behaviours and copied the formula over to them without issue.

I'm not sure if the worksheets you've had have had incident number on them, it simply started at 1 for the first row then the formula is the cell above+1 to give a unique identifier.

Sorry for double post, just wanted to see if anyone had any ideas. I don't see why it doesn't work just the same as all the other columns?

Edit: i've got it to work by recreating the incident number column but I've noticed one final little problem. Every row has an incident number regardless of whether there is an incident for that day. I don't have time to look at it in depth now but I will need to find a way to get the incident number to only generate if one of the behaviour columns has a number present in it.