Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108024 > unrolled thread

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

Back to article view | Back to microsoft.public.excel.programming

Contents

  trying to sum range in one sheet depending on the date in cell on other sheet tomtic56 <tomtic56.10481cf8@excelbanter.com> - 2015-09-23 09:47 +0100
    Re: trying to sum range in one sheet depending on the date in cell on other sheet Claus Busch <claus_busch@t-online.de> - 2015-09-23 12:20 +0200

#108024 — 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




-- 
tomtic56

[toc] | [next] | [standalone]

#108025

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

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web