I have a 3 column table with 20 rows. The first two columns contain travel
expense data. The cells in the third column are blank exept for whichever
cell I enter an "x" in. Please let me what formula or worksheet function do
I use to calculate the travel expenses based on the data in the first two
columns that correspond to the row of the cell with an "x" in the third
column?
Thank You!
Assuming the x in column F, expense data in D & E, then
=SUMPRODUCT((F2:F20="x")*(D2:E20))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Mr. Jan Park" <[email protected]> wrote in message
news:[email protected]...
> I have a 3 column table with 20 rows. The first two columns contain
travel
> expense data. The cells in the third column are blank exept for whichever
> cell I enter an "x" in. Please let me what formula or worksheet function
do
> I use to calculate the travel expenses based on the data in the first two
> columns that correspond to the row of the cell with an "x" in the third
> column?
>
> Thank You!
Try this:
=SUMPRODUCT((C1:C20="X")*A1:B20)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Mr. Jan Park" <[email protected]> wrote in message
news:[email protected]...
> I have a 3 column table with 20 rows. The first two columns contain
travel
> expense data. The cells in the third column are blank exept for whichever
> cell I enter an "x" in. Please let me what formula or worksheet function
do
> I use to calculate the travel expenses based on the data in the first two
> columns that correspond to the row of the cell with an "x" in the third
> column?
>
> Thank You!
Mr. Phillips,
Thanks for your response. Unfortunately, my original description was too
simplistic. The precise operation I need to do for the row of expense data
of which the cell in column F contain an "X" is, "=D?+(E?*4)" with "?"
representing the # of the row that contains the "X" in column F.
Jan park
"Bob Phillips" wrote:
> Assuming the x in column F, expense data in D & E, then
>
> =SUMPRODUCT((F2:F20="x")*(D2:E20))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Mr. Jan Park" <[email protected]> wrote in message
> news:[email protected]...
> > I have a 3 column table with 20 rows. The first two columns contain
> travel
> > expense data. The cells in the third column are blank exept for whichever
> > cell I enter an "x" in. Please let me what formula or worksheet function
> do
> > I use to calculate the travel expenses based on the data in the first two
> > columns that correspond to the row of the cell with an "x" in the third
> > column?
> >
> > Thank You!
>
>
>
Then maybe in G2 enter:
=IF(F2="X",D2+(E2*4),0)
copy down as required.
Hope this helps
Rowan
Mr. Jan Park wrote:
> Mr. Phillips,
> Thanks for your response. Unfortunately, my original description was too
> simplistic. The precise operation I need to do for the row of expense data
> of which the cell in column F contain an "X" is, "=D?+(E?*4)" with "?"
> representing the # of the row that contains the "X" in column F.
>
> Jan park
>
> "Bob Phillips" wrote:
>
>
>>Assuming the x in column F, expense data in D & E, then
>>
>>=SUMPRODUCT((F2:F20="x")*(D2:E20))
>>
>>--
>>
>>HTH
>>
>>RP
>>(remove nothere from the email address if mailing direct)
>>
>>
>>"Mr. Jan Park" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>I have a 3 column table with 20 rows. The first two columns contain
>>
>>travel
>>
>>>expense data. The cells in the third column are blank exept for whichever
>>>cell I enter an "x" in. Please let me what formula or worksheet function
>>
>>do
>>
>>>I use to calculate the travel expenses based on the data in the first two
>>>columns that correspond to the row of the cell with an "x" in the third
>>>column?
>>>
>>>Thank You!
>>
>>
>>
Then you can try this:
=SUMPRODUCT((F2:F20="X")*E2:E20*4+(F2:F20="X")*D2:D20)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Mr. Jan Park" <[email protected]> wrote in message
news:[email protected]...
> Mr. Phillips,
> Thanks for your response. Unfortunately, my original description was too
> simplistic. The precise operation I need to do for the row of expense
data
> of which the cell in column F contain an "X" is, "=D?+(E?*4)" with "?"
> representing the # of the row that contains the "X" in column F.
>
> Jan park
>
> "Bob Phillips" wrote:
>
> > Assuming the x in column F, expense data in D & E, then
> >
> > =SUMPRODUCT((F2:F20="x")*(D2:E20))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Mr. Jan Park" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a 3 column table with 20 rows. The first two columns contain
> > travel
> > > expense data. The cells in the third column are blank exept for
whichever
> > > cell I enter an "x" in. Please let me what formula or worksheet
function
> > do
> > > I use to calculate the travel expenses based on the data in the first
two
> > > columns that correspond to the row of the cell with an "x" in the
third
> > > column?
> > >
> > > Thank You!
> >
> >
> >
Thank You!! It works!!
"RagDyer" wrote:
> Then you can try this:
>
> =SUMPRODUCT((F2:F20="X")*E2:E20*4+(F2:F20="X")*D2:D20)
>
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Mr. Jan Park" <[email protected]> wrote in message
> news:[email protected]...
> > Mr. Phillips,
> > Thanks for your response. Unfortunately, my original description was too
> > simplistic. The precise operation I need to do for the row of expense
> data
> > of which the cell in column F contain an "X" is, "=D?+(E?*4)" with "?"
> > representing the # of the row that contains the "X" in column F.
> >
> > Jan park
> >
> > "Bob Phillips" wrote:
> >
> > > Assuming the x in column F, expense data in D & E, then
> > >
> > > =SUMPRODUCT((F2:F20="x")*(D2:E20))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Mr. Jan Park" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a 3 column table with 20 rows. The first two columns contain
> > > travel
> > > > expense data. The cells in the third column are blank exept for
> whichever
> > > > cell I enter an "x" in. Please let me what formula or worksheet
> function
> > > do
> > > > I use to calculate the travel expenses based on the data in the first
> two
> > > > columns that correspond to the row of the cell with an "x" in the
> third
> > > > column?
> > > >
> > > > Thank You!
> > >
> > >
> > >
>
>
You're welcome, and thank you for the feed-back.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mr. Jan Park" <[email protected]> wrote in message
news:[email protected]...
> Thank You!! It works!!
>
> "RagDyer" wrote:
>
> > Then you can try this:
> >
> > =SUMPRODUCT((F2:F20="X")*E2:E20*4+(F2:F20="X")*D2:D20)
> >
> > --
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "Mr. Jan Park" <[email protected]> wrote in message
> > news:[email protected]...
> > > Mr. Phillips,
> > > Thanks for your response. Unfortunately, my original description was
too
> > > simplistic. The precise operation I need to do for the row of expense
> > data
> > > of which the cell in column F contain an "X" is, "=D?+(E?*4)" with "?"
> > > representing the # of the row that contains the "X" in column F.
> > >
> > > Jan park
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Assuming the x in column F, expense data in D & E, then
> > > >
> > > > =SUMPRODUCT((F2:F20="x")*(D2:E20))
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Mr. Jan Park" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > I have a 3 column table with 20 rows. The first two columns
contain
> > > > travel
> > > > > expense data. The cells in the third column are blank exept for
> > whichever
> > > > > cell I enter an "x" in. Please let me what formula or worksheet
> > function
> > > > do
> > > > > I use to calculate the travel expenses based on the data in the
first
> > two
> > > > > columns that correspond to the row of the cell with an "x" in the
> > third
> > > > > column?
> > > > >
> > > > > Thank You!
> > > >
> > > >
> > > >
> >
> >
Thank You!! It works!!
"RagDyer" wrote:
> Then you can try this:
>
> =SUMPRODUCT((F2:F20="X")*E2:E20*4+(F2:F20="X")*D2:D20)
>
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Mr. Jan Park" <[email protected]> wrote in message
> news:[email protected]...
> > Mr. Phillips,
> > Thanks for your response. Unfortunately, my original description was too
> > simplistic. The precise operation I need to do for the row of expense
> data
> > of which the cell in column F contain an "X" is, "=D?+(E?*4)" with "?"
> > representing the # of the row that contains the "X" in column F.
> >
> > Jan park
> >
> > "Bob Phillips" wrote:
> >
> > > Assuming the x in column F, expense data in D & E, then
> > >
> > > =SUMPRODUCT((F2:F20="x")*(D2:E20))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Mr. Jan Park" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a 3 column table with 20 rows. The first two columns contain
> > > travel
> > > > expense data. The cells in the third column are blank exept for
> whichever
> > > > cell I enter an "x" in. Please let me what formula or worksheet
> function
> > > do
> > > > I use to calculate the travel expenses based on the data in the first
> two
> > > > columns that correspond to the row of the cell with an "x" in the
> third
> > > > column?
> > > >
> > > > Thank You!
> > >
> > >
> > >
>
>
You're welcome, and thank you for the feed-back.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mr. Jan Park" <[email protected]> wrote in message
news:[email protected]...
> Thank You!! It works!!
>
> "RagDyer" wrote:
>
> > Then you can try this:
> >
> > =SUMPRODUCT((F2:F20="X")*E2:E20*4+(F2:F20="X")*D2:D20)
> >
> > --
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "Mr. Jan Park" <[email protected]> wrote in message
> > news:[email protected]...
> > > Mr. Phillips,
> > > Thanks for your response. Unfortunately, my original description was
too
> > > simplistic. The precise operation I need to do for the row of expense
> > data
> > > of which the cell in column F contain an "X" is, "=D?+(E?*4)" with "?"
> > > representing the # of the row that contains the "X" in column F.
> > >
> > > Jan park
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Assuming the x in column F, expense data in D & E, then
> > > >
> > > > =SUMPRODUCT((F2:F20="x")*(D2:E20))
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Mr. Jan Park" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > I have a 3 column table with 20 rows. The first two columns
contain
> > > > travel
> > > > > expense data. The cells in the third column are blank exept for
> > whichever
> > > > > cell I enter an "x" in. Please let me what formula or worksheet
> > function
> > > > do
> > > > > I use to calculate the travel expenses based on the data in the
first
> > two
> > > > > columns that correspond to the row of the cell with an "x" in the
> > third
> > > > > column?
> > > > >
> > > > > Thank You!
> > > >
> > > >
> > > >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks