Hi all, I have a spreadsheet and am trying to calculate hours worked using 2 sheets.
sheet1 is entitlement calculation and sheet2 is public holidays

I am trying to sum the hours worked on sheet2 "public holiday" range c2 ; to c9 depending on the date on cell B12 of sheet 1 "entitlement calculation"
starting in with the date in sheet 1 "entitlement calculation" cell b12 as shown in the sample below

SAMPLE: Sheet 1.."entitlement calculation" cell B12 is the start date which could be the 1st of any month..in the sample case it is 01-Oct-2015 which then sums the range hours worked (c2:c9) starting at date 30-sep-2015 (c6) which gives 7 hours which is correct,
however the next entry into cell B12 might be 01-may-2015 which would sum range hours worked from 30-apr-2015 which would be (c4:c9) which would give 10.5 hours.


sheet 2 is public holidays as shown below.

COL A COL B COL C
Day Date Hours worked
Friday 03/04/2015 3.5
Monday 06/04/2015 0
Monday 04/05/2015 3.5
Monday 28/09/2015 0
Friday 25/12/2015 3.5
Monday 28/12/2015 0
Friday 01/01/2016 3.5
Monday 04/01/2016 0

I have tried using various if statements but cant get the correct answer for all options..any help would be much appreciated..thanks

Hi,
Am Wed, 23 Sep 2015 09:47:39 +0100 schrieb tomtic56:

COL A COL B COL C
Day Date Hours worked
Friday 03/04/2015 3.5
Monday 06/04/2015 0
Monday 04/05/2015 3.5
Monday 28/09/2015 0
Friday 25/12/2015 3.5
Monday 28/12/2015 0
Friday 01/01/2016 3.5
Monday 04/01/2016 0

try in "public holiday"
=SUMIF('entitlement calculation'!B:B,"="&B12,'entitlement calculation'!C:C)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional