Hello

I attach a sample of a workbook I am having problems with. I have a list of data (worksheet "All Data") and would like to summarise this (worksheet "Summary") - see attached.

I have come up with a formula, but am having trouble copying it to other cells.
In its current form, i.e.=SUMIFS('All Data'!$D:$D,'All Data'!$A:$A,"ABELOV",'All Data'!$C:$C, ">=29/12/2014",'All Data'!$C:$C,"<5/1/2015") the formula returns the correct value for cell C6 in the worksheet "Summary". However, I can't copy it across and down correctly.

I would like to be able to substitute:
the contents of cell A6 for "ABELOV";
the contents of cell C5 for ">=29/12/2014"; and
the contents of cell D5 for "<5/1/2015"
so that I can copy the formula across and down without having to type the names of the different Administrators or the date ranges. However, I cannot seem to do this.

Can someone help?

Thanks.

Taryn

PS I am using Excel 2010 on Windows Professional 7

Attached Files

Time Sheet Report.2.xlsx (434.7 KB, 8 views)

First off, remove the space from the first tab. It will make you equations more aesthetic. Doing so and making it so the equation is copyable results in:

=SUMIFS(AllData!$C:$C,AllData!$A:$A,$A6,AllData!$B :$B, ">=" &C$5,AllData!$B:$B,"<" & D$5)

I added some more date headers as the equations in Column E, for example, refer to the date header in Column F.

Attached Files

Time Sheet Report.2.xlsx (433.2 KB, 27 views)

Perfect!

Thank you very much for your help (and your promptness!!)

Taryn