Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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! |
#2
![]() |
|||
|
|||
![]()
Try this:
=SUMPRODUCT((C1:C20="X")*A1:B20) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Mr. Jan Park" wrote in message ... 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! |
#3
![]() |
|||
|
|||
![]()
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" wrote in message ... 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! |
#4
![]() |
|||
|
|||
![]()
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" wrote in message ... 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! |
#5
![]() |
|||
|
|||
![]()
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" wrote in message ... 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! |
#6
![]() |
|||
|
|||
![]()
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" wrote in message ... 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" wrote in message ... 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! |
#7
![]() |
|||
|
|||
![]()
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" wrote in message ... 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" wrote in message ... 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! |
#8
![]() |
|||
|
|||
![]()
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Cell to follow content AND/OR formating of another cell | Excel Discussion (Misc queries) | |||
Return cell contents based on conditional lookup | Excel Worksheet Functions | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
how to set up one cell that assigns a value based on the value of another cell | Excel Worksheet Functions | |||
Put an autoshape in a cell based on another cells content | Excel Worksheet Functions |