# How to "dual and" sumif function

#### hometech99

##### New Member
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

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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 ...??

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

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. ]

hometech99 said:
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?

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

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.

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 ???

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.

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.

Replies
7
Views
298
Replies
8
Views
292
Replies
11
Views
2K
Replies
0
Views
469
Replies
0
Views
227

1,218,845
Messages
6,144,808
Members
450,568
Latest member
excelnewlearner

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back