I've searched around and read various stuff on countif's, and dcount but I don't think they quite solve my problem.
I have two worksheets, one is a daily recording of patients behaviour with incidents of various types of behaivour being recorded as a number in different columns. Seperate incidents on the same day are entered on a seperate row, so 14/01/2013 may have 3 rows for 3 behaviours and 15/01/2013 may have none or 5 or whatever.
The second worksheet is a tally of the weekly occurance of each behaviour, thus it records the incidences where a number greater than 0 is present in the relevant column and tallies that with all incidences for that week (this is currently done manually and is the formula I am after).
A countif can easily be used to count the number of records between a given set of cells (thus dates) but this only works for the past as for future weeks there is no way of knowing what the cell range for a given week will be as each new incident creates a new line.
What i'm looking for is a formula that does:
Counts the number of cells with a value greater than 0 in a given column that is between the date 01/01/2013 and 08/01/2013 and automatically adjusts the cell range to encompass those dates (so it uses the date from column A and adds the records from column B,C,D or whichever). The data is taken from the first worksheet and displayed on the second. The date range is also present on the second worksheet in column A thus: 01/01/2013-08/01/2013 to give the week it covers.
The formula needs to be able to be dragged down to the cells so that when I enter the data into the raw data file it automatically adjusts the cell range on the weekly worksheet and adds it up.
I hope that makes sense as is possible! Thanks.
Using excel 2003.
Bookmarks