Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mileage Calculation
Hello,
I need to calculate the current IRS mileage rate and then have a combined total by fac# example: 11/01 10 miles to be charged to fac# 111 11/02 10 miles to be charged to fac# 222 11/03 10 miles to be charged to fac# 111 11/04 10 miles to be charged to fac# 222 The form we are currently using does calculate the mileage rate for each line but does not combine all the mileage on different dates to the same fac. Ideally, the total column would only show 111 & 222 once. Possible? Thank you Cathy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mileage Calculation
Hi!
11/01 10 miles to be charged to fac# 111 Is that all in one cell? Biff "Cathy Landry" wrote in message ... Hello, I need to calculate the current IRS mileage rate and then have a combined total by fac# example: 11/01 10 miles to be charged to fac# 111 11/02 10 miles to be charged to fac# 222 11/03 10 miles to be charged to fac# 111 11/04 10 miles to be charged to fac# 222 The form we are currently using does calculate the mileage rate for each line but does not combine all the mileage on different dates to the same fac. Ideally, the total column would only show 111 & 222 once. Possible? Thank you Cathy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mileage Calculation
Hi Biff,
No, sorry I wasn't more clear. This is how it would be setup COLUMNS: A B C D F G H DATE MILES FAC# TOTAL | TOTAL BY FAC 11/01 10 111 4.85 | FAC# Ttl MLS TOTAL 11/02 10 222 4.85 | 111 20 9.70 11/03 10 111 4.85 | 222 20 9.70 11/04 10 222 4.85 | "Biff" wrote: Hi! 11/01 10 miles to be charged to fac# 111 Is that all in one cell? Biff "Cathy Landry" wrote in message ... Hello, I need to calculate the current IRS mileage rate and then have a combined total by fac# example: 11/01 10 miles to be charged to fac# 111 11/02 10 miles to be charged to fac# 222 11/03 10 miles to be charged to fac# 111 11/04 10 miles to be charged to fac# 222 The form we are currently using does calculate the mileage rate for each line but does not combine all the mileage on different dates to the same fac. Ideally, the total column would only show 111 & 222 once. Possible? Thank you Cathy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mileage Calculation
Hi Cathy
Try =SUMPRODUCT(--($C$2:$C$100=111),$B$2:$B$100) for total miles =SUMPRODUCT(--($C$2:$C$100=111),$D$2:$D$100) for total value change length of ranges to suit. Substitute cell reference holding 111 or 222 to make it easier to pick up the the individual FAC# numbers =SUMPRODUCT(--($C$2:$C$100=$F2),$B$2:$B$100) for Miles and FAC#111 Regards Roger Govier Cathy Landry wrote: Hi Biff, No, sorry I wasn't more clear. This is how it would be setup COLUMNS: A B C D F G H DATE MILES FAC# TOTAL | TOTAL BY FAC 11/01 10 111 4.85 | FAC# Ttl MLS TOTAL 11/02 10 222 4.85 | 111 20 9.70 11/03 10 111 4.85 | 222 20 9.70 11/04 10 222 4.85 | "Biff" wrote: Hi! 11/01 10 miles to be charged to fac# 111 Is that all in one cell? Biff "Cathy Landry" wrote in message ... Hello, I need to calculate the current IRS mileage rate and then have a combined total by fac# example: 11/01 10 miles to be charged to fac# 111 11/02 10 miles to be charged to fac# 222 11/03 10 miles to be charged to fac# 111 11/04 10 miles to be charged to fac# 222 The form we are currently using does calculate the mileage rate for each line but does not combine all the mileage on different dates to the same fac. Ideally, the total column would only show 111 & 222 once. Possible? Thank you Cathy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mileage Calculation
Thank you Roger!
"Roger Govier" wrote: Hi Cathy Try =SUMPRODUCT(--($C$2:$C$100=111),$B$2:$B$100) for total miles =SUMPRODUCT(--($C$2:$C$100=111),$D$2:$D$100) for total value change length of ranges to suit. Substitute cell reference holding 111 or 222 to make it easier to pick up the the individual FAC# numbers =SUMPRODUCT(--($C$2:$C$100=$F2),$B$2:$B$100) for Miles and FAC#111 Regards Roger Govier Cathy Landry wrote: Hi Biff, No, sorry I wasn't more clear. This is how it would be setup COLUMNS: A B C D F G H DATE MILES FAC# TOTAL | TOTAL BY FAC 11/01 10 111 4.85 | FAC# Ttl MLS TOTAL 11/02 10 222 4.85 | 111 20 9.70 11/03 10 111 4.85 | 222 20 9.70 11/04 10 222 4.85 | "Biff" wrote: Hi! 11/01 10 miles to be charged to fac# 111 Is that all in one cell? Biff "Cathy Landry" wrote in message ... Hello, I need to calculate the current IRS mileage rate and then have a combined total by fac# example: 11/01 10 miles to be charged to fac# 111 11/02 10 miles to be charged to fac# 222 11/03 10 miles to be charged to fac# 111 11/04 10 miles to be charged to fac# 222 The form we are currently using does calculate the mileage rate for each line but does not combine all the mileage on different dates to the same fac. Ideally, the total column would only show 111 & 222 once. Possible? Thank you Cathy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mileage Calculation
Hi Cathy
You're very welcome. Glad it worked for you. Thanks for the feedback. Regards Roger Govier Cathy Landry wrote: Thank you Roger! "Roger Govier" wrote: Hi Cathy Try =SUMPRODUCT(--($C$2:$C$100=111),$B$2:$B$100) for total miles =SUMPRODUCT(--($C$2:$C$100=111),$D$2:$D$100) for total value change length of ranges to suit. Substitute cell reference holding 111 or 222 to make it easier to pick up the the individual FAC# numbers =SUMPRODUCT(--($C$2:$C$100=$F2),$B$2:$B$100) for Miles and FAC#111 Regards Roger Govier Cathy Landry wrote: Hi Biff, No, sorry I wasn't more clear. This is how it would be setup COLUMNS: A B C D F G H DATE MILES FAC# TOTAL | TOTAL BY FAC 11/01 10 111 4.85 | FAC# Ttl MLS TOTAL 11/02 10 222 4.85 | 111 20 9.70 11/03 10 111 4.85 | 222 20 9.70 11/04 10 222 4.85 | "Biff" wrote: Hi! 11/01 10 miles to be charged to fac# 111 Is that all in one cell? Biff "Cathy Landry" wrote in message ... Hello, I need to calculate the current IRS mileage rate and then have a combined total by fac# example: 11/01 10 miles to be charged to fac# 111 11/02 10 miles to be charged to fac# 222 11/03 10 miles to be charged to fac# 111 11/04 10 miles to be charged to fac# 222 The form we are currently using does calculate the mileage rate for each line but does not combine all the mileage on different dates to the same fac. Ideally, the total column would only show 111 & 222 once. Possible? Thank you Cathy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage calculation | Excel Discussion (Misc queries) | |||
Commission Calculation with IF Stmt. | Excel Worksheet Functions | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |