Formula for mileage log?

[michaelas]

Hi
I'm using Excel to record auto mileage.
I have it set up well enough except for one thing.
Please refer to the screenshot.

Mileage Log example.jpg

Columns A – D are self-explanatory
Column E is the total miles for the day
Column F describes gen'l info for the day.
Cell G 2 is total business miles
Cell H 2 (highlighted in screenshot) is total “rec” or recreation miles

Notice the formula for H 2 –> =SUMIF(F:F,”rec”,E:E)
This means that if I type rec in column F, as in F 3, the miles are considered recreation miles, and therefore are recorded in cell H 3. Conversely, if rec is not typed in, (as in cell F 4), then the resulting mileage goes to cell G 3. So far so good.

The problem is, if I want to type in rec and additional info, eg “rec, went to store”, then the mileage shifts to column G instead of H. ie, The rec miles become business miles.

My question is, how do I amend the formula so it will record “rec” miles, but I can also write other info in the same cell in Column F.

Hope this is clear. Thanks in advance for your help.
M

[protonLeah]

Please replace that screenshot with a copy of the workbook. Pictures can't be copied into Excel, that means that anyone who wants to help would have to create their own version of your wb.

[michaelas]

here is a copy of the workbook page attached M

Test mileage log.xls.

[Palmetto]

My question is, how do I amend the formula so it will record “rec” miles, but I can also write other info in the same cell in Column F.

I would maintain a separate column to ID the type of mileage and add a column to capture comments about the mileage.

BTW, it is not a good practice to reference entire columns in formulas, as with =SUMIF(F:F,”rec”,E:E)
You should specify the range, ie. F2:F100 and E2:E100.

However, I would opt for using a Pivot Table to calculate mileage for the various categories.

[michaelas]

Thank you for your reply