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

 

[santeria]

So, you need a cell with Claimable miles, and then a Tally I guess.

So, it sounds like an IF formula should cover you.

As in[for example] =IF(G1>41,G1-41,"")


Maybe ...??

 

[hometech99]

but how do i tally the result of each IF result, and how do i get the IF function to run through each row?

 

[santeria]

Oh, okay... I thought you had the Mile-age totals for each day... in which case I had presumed that you just out ther IF formula in the next cell to the right, change the Cell ref to whatever is the cell on the Left, then SUM the total claimable miles at the bottom of the column...


Do you need a Mileage template ??

[ And the IF Formula would go in each cell... Just put it in the first one with the proper cell address, then drag the cell box down until the final entry, and then insert the SUM formula for the Entire number of cells. ]

 

[Brian from Maui]

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

So if you travel 50 miles, you only get reimbursed for 9 miles at a certain rate? Or the whole 50 miles?

 

[hometech99]

hi
i do have a mileage total for each day- thats what the subtotal function gave me.
say i drove to a site, then home.

that give 2 rows (one for each leg of the trip).
then, i did a subtotal , for each change in date, sum the trip leg lengths. so, theres a third row with column a having the date and the word "Total", then under the length column, the total miles for the day.

i tried this in the first row, but dont know how to make it calculate, then copy it to each row in the sheet:

IF(A9="*Total",IF(D9>41,D9-41,""),"")

thanks

 

[hometech99]

yes, i only get reimbursed for the miles OVER 41. so, if i drove 50 miles in a day, i get reimbursed for the 9 miles.
the other 41 i submit to IRS.

my tax guy said if the company will pay you, do it, rather than just forgoing it and submitting all 50 to IRS.

 

[santeria]

The IF formula just has to reference each days total, so you pick that cell,
then once done for the first cell, just use the drag method to populate the formula in each cell, so long as a total exist in the column to the left, then the formula in the right side will autocalculate...

Is that clear? Or no ???

 

[hometech99]

yes. im dragging it down the cloumn, but its not populating. i understandd the idea you are saying.

is my formula correct, if the rows i want to use have the word "Total" in the date field?

the date field is column A. The length field is column D.

so, im trying to get a calculation ONLY on the row that has the word Total in it.

 

[hometech99]

got it.
rather than trying to hunt for the word "Total", ill just go to the next cloumn, the STart mileage. that is blank on the subtotal lines, and easier to search for.

so, its calculating a result correctly

now, still cant get it to drag.