Mileage Calculation

[Cathy Landry]

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

[Biff]

Hi!

>11/01 10 miles to be charged to fac# 111

Is that all in one cell?

Biff

"Cathy Landry" <CathyLandry@discussions.microsoft.com> wrote in message
news:1E0CEA12-9BFF-46D9-866C-DD6917D456BA@microsoft.com...
> 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

[Cathy Landry]

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" <CathyLandry@discussions.microsoft.com> wrote in message
> news:1E0CEA12-9BFF-46D9-866C-DD6917D456BA@microsoft.com...
> > 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
>
>
>

[Roger Govier]

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" <CathyLandry@discussions.microsoft.com> wrote in message
>>news:1E0CEA12-9BFF-46D9-866C-DD6917D456BA@microsoft.com...
>>
>>>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
>>
>>
>>

[Cathy Landry]

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" <CathyLandry@discussions.microsoft.com> wrote in message
> >>news:1E0CEA12-9BFF-46D9-866C-DD6917D456BA@microsoft.com...
> >>
> >>>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
> >>
> >>
> >>
>

[Roger Govier]

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" <CathyLandry@discussions.microsoft.com> wrote in message
>>>>news:1E0CEA12-9BFF-46D9-866C-DD6917D456BA@microsoft.com...
>>>>
>>>>
>>>>>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
>>>>
>>>>
>>>>