Calculating mileage driven from odometer readings

[MARKSTRO]

I have data with the employee # in A2:A5050, in E2:E5050 are the odometer readings taken that month as the driver fills up several times that month.

How can I calculate total miles driven using a Sumif with a Min/Max calculation for each of 360 employees in Column A with his odometer inputs from fueling throughout the month in Column E.

For employee # 1 do a Max in E subtracting the Min>0

[Pete_UK]

This array* formula will give you the max reading for employee # 1:

=MAX(IF(A$2:A$5050=1,E$2:E$5050))

and similarly, this array* formula will give you the lowest reading for employee # 1:

=MIN(IF(A$2:A$5050=1,E$2:E$5050))

So, you can just put them together (still an array* formula):

=MAX(IF(A$2:A$5050=1,E$2:E$5050)) - MIN(IF(A$2:A$5050=1,E$2:E$5050))

You might like to put the employee number in a cell somewhere and refer to that cell in the formula rather than the 1 in the example, and then you can copy the formula down.

*An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>

Hope this helps.

Pete

[MARKSTRO]

I'll give it a shot, results posted soon. Thanks

[MARKSTRO]

Thanks, works fine. How would I tell the array to ignore 0 values in the E:E range, can't seem to fit it in?

[Pete_UK]

Do it like this:

=MAX(IF((A$2:A$5050=1)*(E$2:E$5050<>0),E$2:E$5050)) - MIN(IF((A$2:A$5050=1)*(E$2:E$5050<>0),E$2:E$5050))

Still an array formula, so use CSE to commit.

Hope this helps.

Pete