using mutiple entries in a calculation

[soma104]

Hi,

I'm trying to create a workbook that will calculate times. Currently I have a worksheet that has, for example Incident #1, Date/Time In, and Date/Time Out. At the end of the row it will calculate total time in hours.

I need to create a second worksheet that will have Incident # to correspond to the incident # on Sheet1, with Date/Time In and Out. However, on this sheet there could be more than one occurance of Incident#1, and multiple times in an out. That total will be deducted from the total of incident#1 on Sheet1.

The easiest way to explain this would be...say for the sake of argument Sheet1 calculates the duration of a vacation in hours (lets say 168 hours). Sheet2 will calculate how much of that time was spent doing business (lets say 2 hours on day1, 3 hours on day 4). In the end I want to subtract 5 hours from 168 hours giving me 163 actual vacation hours.

My problem is, I don't know how to go about using the values in multiple rows in Sheet2 that correspond to the row in Sheet1.

Hopefully this makes sense to more than just me
Soma

[DonkeyOte]

Could you not conduct a SUMIF on Sheet2 based on Incident # ?

Subtract the result from your row value on Sheet1.

(assuming you have a column on Sheet2 that's already calculating the net of the In/Out times of course)

[redstang423]

Am I understanding that sheet 1 has the overall start and finish time, and sheet two has more detailed information, such as the duration of any breaks taken, etc.?

It sounds like you might be able to get what you are looking for with the SUMIF formula. The arguments are (range to meet the if criteria, if criteria, range to sum). The formula would look something like this...

=SUMIF(Worksheet2 incident ID column, =specific incident ID cell from worksheet1, Worksheet2 timelapse between in and out column)

Depending on how your spreadsheet is set up, it will get you the amount of time that was spent either working or not working. If you can't add a column that finds the time lapse time out-time in) for logistical reasons, change the last argument to the "time out" column then in the same formula, subtract off the identical formula with the third argument referring to the "in" column.

If this doesn't make sense, let me know. If you provide columns that the data sits in, I can make the formula more specific.

[soma104]

Hi again,

I guess I'm not quite there yet. I tried the following but it won't work for me.

=SUMIF(Sheet2!A:A,"=A1",Sheet2!F:F)

I tried to use the formula on Sheet1 where the main data is. The formula worked when it was in Sheet2, but not in Sheet1.

Soma

[DonkeyOte]

You don't need the = operator and given the reference A1 is not a literal string it should not be encased within quotations/speech marks.

=SUMIF(Sheet2!A:A,A1,Sheet2!F:F)

[soma104]

Thank you again, I made the changes you suggested, and now it works perfectly.

Soma

[soma104]

Thank you, and thank you...

It's exactly what I needed. I am going to try and work it into my formulas. If I have any problems, I'll repost with more detail. But I'm hopeful.

Soma