Excel 2007 : Calculate times between a date range and other categories...

**Boozshey** · 06-22-2010, 08:11 PM

I have attached the file I am working with.

The basic of what I need to do is take the data from Sheet2 and have it total on Sheet1.

Where it get's complex however is that I need that data to be pulled only when certain criteria are met.

So let me try and explain:

You will notice RAW data on Sheet 2, which includes a Date, Position, Block, Live Sim, Non-Pos.

On Sheet1 I need to pull the RAW data from Sheet2 and calculate it in the appropriate blocks after meeting the correct criteria.

So cell B9 on Sheet1 would need to contain the total LIVE hours from Sheet2 within the date range of 27 May-29 May and I would like for it to determine that by 2 criterias (Date, Block) When those three criterias from Sheet2 match it should total it there.

Then cell D9 would operate the same way, but would total the entire LIVE hours from the LIVE column of Sheet2.

Then B12 would be the total of B9:11 and D12 would be the total of D9:11.

The times need to stay in an hour+minute format such as 1+45 or 1:45 for 1 hour 45 minutes.

Any help would be greatly appreciated.

**DonkeyOte** · 06-23-2010, 03:25 AM

Given use of XL2007 you should make use of the SUMIFS function, eg:

B9:
=SUMIFS(Sheet2!$C$2:$C$6,Sheet2!$A$2:$A$6,">="&$D$2,Sheet2!$A$2:$A$6,"<="&$E$2,Sheet2!$B$2:$B$6,$C$2)

Apply a custom format to B9 (etc) of [hh]:mm:ss

(Note you specify 27 -> 29th yet file imples 27 - 30th - if the end date is meant to be first day to be excluded change <= to < in the above)

Modify ranges as appropriate of course.

For more info. on SUMIFS see XL Help (and/or Google)

**Boozshey** · 06-23-2010, 03:03 PM

For us simple minded people, you sir are a GENIUS!!

Thank you very very much!!!

**Boozshey** · 07-10-2010, 05:19 PM

I am trying to edit a new critera and can't figure it out.

I need B18 to calculate =sum(B6+B17+B15) if C2 = I and if C2 = II then I need B18 to enter the same cell data as E18???

Thanks again for any help....

(it's embedded in Word since it's what I will be using it in eventually.)