trying to sum range in one sheet depending on the date in cell on other sheet
 

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

trying to sum range in one sheet depending on the date in cell on other sheet
 

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