+ Reply to Thread
Results 1 to 8 of 8

Based on Condition Track ID, Values to be return in Column C

  1. #1
    Forum Contributor
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    131

    Based on Condition Track ID, Values to be return in Column C

    Hi,

    Required a formula help for to be return a values as per conditions based on Track ID. I have a data set like column A to D contains Headers (Track ID, Total Qty, Allowable value, Actual value),and in column G to H (Track ID, Rates)

    As per track ID ,there mentioned total quantities (In column B) to be multiply with rates (Column H) and return the values in column C, and some where there is no rates are available for example (Track ID-321563,215632)
    does not have the rates, at that condition the same values to be return in column C which are exist in Column D.


    Sample sheets with expected results are enclosed.


    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Based on Condition Track ID, Values to be return in Column C

    Try below formula in C17 copy and paste towards down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Based on Condition Track ID, Values to be return in Column C

    Please try at C4
    =--TEXT(VLOOKUP(A4:A11,G3:H6,2,0)*B4:B11,"0.00;;"&D4:D11)
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    131

    Re: Based on Condition Track ID, Values to be return in Column C

    Many Thanks to Bo_ry,Samba ravi for your formula,

    I was forgot to add this other condition in previous post the above said condition only apply to particular (Track ID-321563,215632), rest of this all to be returns as usual calculation, means (Total Quantity * Rate) return in column C , If no rate available for that particular track ID then no needs to return column D actual value.

    Sample sheets with modified expected results are enclosed.


    Thanks,
    Attached Files Attached Files

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Based on Condition Track ID, Values to be return in Column C

    =ifna(--text(vlookup(a4:a13,g3:h6,2,0)*b4:b13,"0.00;;"&d4:d13),0)
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    131

    Re: Based on Condition Track ID, Values to be return in Column C

    Thanks for the response,

    may be it should be g3:h8 not g3:h6, then C:11,C:13 returns Zero (because of no rates are available those Track IDs) that's what i required as result.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Based on Condition Track ID, Values to be return in Column C

    I have 3 formulas to get the same result.

    =IFNA(VLOOKUP(A4:A13,G5:H8,2,0)*B4:B13,D4:D13)

    =IFNA(--TEXT(VLOOKUP(A4:A13,G3:H6,2,0)*B4:B13,"0.00;;"&D4:D13),0)

    =IF(COUNTIFS(G3:G4,A4:A13),D4:D13,VLOOKUP(A4:A13,G3:H8,2,0)*B4:B13)
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    131

    Re: Based on Condition Track ID, Values to be return in Column C

    Thanks a lot bo_ry for the formulas,

    almost solved if any thing else required in this query i can revert back to you.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Return Column Letter based on condition
    By SteveTheFish in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-15-2019, 12:12 AM
  2. Replies: 3
    Last Post: 08-07-2018, 10:14 AM
  3. [SOLVED] If condition met, return all values from column
    By PeakyBlinder in forum Excel General
    Replies: 8
    Last Post: 04-04-2018, 03:55 AM
  4. Replies: 2
    Last Post: 07-04-2017, 03:20 PM
  5. Counting unique values based on condition in a different column
    By rafuk73 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2014, 02:59 PM
  6. How to copy one column values to another column based on condition that row2 contain
    By sanjay19961 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-13-2010, 10:32 AM
  7. Add values in a column based on condition
    By finder003 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-28-2007, 07:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1