How to "dual and" sumif function

hometech99

New Member
Joined
Jan 14, 2004
Messages
8
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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 ...??

(y)
 

hometech99

New Member
Joined
Jan 14, 2004
Messages
8
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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. ]


(y)
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

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?
 

hometech99

New Member
Joined
Jan 14, 2004
Messages
8
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

New Member
Joined
Jan 14, 2004
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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 ???


(y)
 

hometech99

New Member
Joined
Jan 14, 2004
Messages
8
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

New Member
Joined
Jan 14, 2004
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top