How to "dual and" sumif function

[hometech99]

I have a list of driving miles. i get re-imbursed for over 41 miles per day at a certain rate.

now, i have each leg of a trip per row in a spreadsheet (date, start mi, end mi, length, destination, car).
i ran the subtotal to show me how many miles per day i drove.
so, for each day, i have a row that shows the date, the word "Total", and, under the length coulmn, the toal miles for the day.

NOW, i need to take that subtotal LENGTH data, and for each LENGTH subtotal thats over 41 miles, i need to total all those "over 41" miles up.

thanks

 

[Zack Barresse]

if you have a total miles, can't you do something like:

=total miles - (41*(countif(range.selection,">41"))

??

maybe i'm not following either.

 

[hometech99]

got it.
the Fill handle (bottom right).

perfect.

at the end of each subtotal line, i have a column thats JUST the miles OVER 41 for the day.

now just to total and calculate.

thanks for walking me through.

best regards

 

[Brian from Maui]

A picture is worth......

Book1
	E	F	G	H	I
12	start	end	length		ttlover41
13	100	145	45	4
14	145	190	45	4
15	190	231	41
16	231	272	41
17					8
Sheet1

 

[hometech99]

Thanks Brian,

i got that part.
what i needed was something that ran that function ONLY on the subtotal rows
(so, looking for over 41 miles per DAY, NOT per leg of the trip).

so, since the Starting mileage cell (column B) is blank in the subtotal rows (the subtotal row only has the date and the word "Total" in Column A, and the total for the day in column D, all i ended up doing is:

IF(B1="",IF(D1>41,D1-41,""),"")


now, every third row (the Subtotal row created by Excel), has a column that has the Over 41/day miles in it.

at the bottom of the sheet, i just totalled that column, multiplied by mileage reimbursement, and im done.




best regards