Path: csiph.com!xmission!news.glorb.com!peer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!Xl.tags.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!local2.nntp.dca.giganews.com!nntp.newsgroupbanter.com!news.newsgroupbanter.com.POSTED!not-for-mail NNTP-Posting-Date: Wed, 23 Sep 2015 03:49:02 -0500 From: tomtic56 Newsgroups: microsoft.public.excel.programming Subject: trying to sum range in one sheet depending on the date in cell on other sheet Date: Wed, 23 Sep 2015 09:47:39 +0100 Message-ID: Organization: ExcelBanter.com User-Agent: X-Newsreader: X-Originating-IP: Lines: 40 X-Usenet-Provider: http://www.giganews.com X-Trace: sv3-kj1OZgX55DQWu2nA9I0VGZMd3KrHTdmHmpR8ncCRtbLOCXVgyyVbMQFfXoWhFjDsJPZp+0WEb4NbwOJ!oZzkQcVwDtQJVG72xGmXjVQ4w5JfhexzDZntakCPifE9zV70cyJjCIOpImmagycdGFtFk9PFl9EH!E/UjFk/WyA== X-Complaints-To: abuse@newsgroupbanter.com X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.40 X-Original-Lines: 24 X-Original-Bytes: 2307 X-Received-Bytes: 2441 X-Received-Body-CRC: 3683137996 Xref: csiph.com microsoft.public.excel.programming:108024 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 -- tomtic56