Average of values across 5 sheets with two criteria.

Hi guys,

Apologies didnt include in the title I'm using Excel 2003.

I have a couple of issues and if its okay will post two threads to make it easier to follow due to my basic skills in Excel.

I have a workbook with 5 sheets. The first sheet is a stat sheet which picks up data from four other spreadsheets. I want to work out the average working days taken to complete a case but this data is across multiple sheets.

So for example Column E in each sheet tells you what type of case it is i.e. investigation. Then column T tells you number of workings day it took to complete the case. To work this out I have used the following formula:

=SUM(SUMIF(Further_Action!E:E,"Investigation",Further_Action!T:T),SUMIF(Court_Application!E:E,"Investigation",Court_Application!T:T),SUMIF(No_Further_Action!E:E,"Investigation",No_Further_Action!T:T),SUMIF(Closed!E:E,"Investigation",Closed!T:T))/SUM(COUNTIF(Further_Action!E:E,"Investigation"),COUNTIF(Court_Application!E:E,"Investigation"),COUNTIF(No_Further_Action!E:E,"Investigation"),COUNTIF(Closed!E:E,"Investigation"))

I know its very basic but its the only way I know how to work out an average in Excel 2003. I need to add a second criteria where on every sheet it looks in column G for date the case was allocated. As I'm only looking to report in the current report year I want it to look for cases where the value in column G is =>01/04/14 but =<31/03/14.

Is this possible? I have taken the formula as far within my own ability so would really appreciate help with the last part please.

Thanks and Kindest Regards

Imran

Hi,

That's the sort of question which really needs an accompanying workbook with expected results manually-calculated and added.

Regards