+ Reply to Thread
Results 1 to 8 of 8

Formula required for return a section wise different objects number of quantities.

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

    Formula required for return a section wise different objects number of quantities.

    Hi,

    Required a formula help for return a section wise different objects number of quantities in Cells K5:P5,which are existing in each Lot, as based on data set available in Column A:F,and also required in Cells k7:P7 joint lookup items quantities values as per section wise.

    Sample data sheet enclosed.


    Thanks for the help.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula required for return a section wise different objects number of quantities.

    In K5, copied across:
    =INDEX(($B$3:$F$7,$B$11:$F$15,$B$19:$F$23),MATCH($J5,$A$3:$A$7,0),MATCH(K$4,$B$2:$F$2,0),1+INT((COLUMNS($A:A)-1)/2))

    What answers do you expect in the cells beginning K7?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    Re: Formula required for return a section wise different objects number of quantities.

    Thankyou for your quick response,


    below sample data sheet enclosed with desired answers.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Formula required for return a section wise different objects number of quantities.

    "QWS ASH" means sum all rows those have "QWS" or "ASH" (row 6 & 7)?
    You expect it to be "POL QWS", row 5+row 7, why?
    Assum it was typo.
    With my logic, try in K5:

    Please Login or Register  to view this content.
    Quang PT

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

    Re: Formula required for return a section wise different objects number of quantities.

    Many thanks bebo for your solution,


    I am actually looking answers for "QWS ASH" sum all rows (row 6 & 7).

    It was typing error for expecting answers to (row 5 & 7).


    Thanks again,

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

    Re: Formula required for return a section wise different objects number of quantities.

    If Lot names are the same for each section

    K7
    =SUM(SUMIFS(INDEX(($B$3:$F$7,$B$11:$F$15,$B$19:$F$23),,MATCH(K6,$B$2:$F$2,),RIGHT(LOOKUP("z",$K$3:K$3))),$A$3:$A$7,MID($J7,{1;5},3)))

    If not
    =SUMPRODUCT((LOOKUP(ROW($A$1:$A$23),ROW($A$1:$A$23)/(LEFT($A$1:$A$23,4)="Sect"),$A$1:$A$23)=LOOKUP("z",$K$3:K$3))*ISNUMBER(MATCH($A$1:$A$23,MID($J7,{1;5},3),)),INDEX($B$1:$F$23,,MATCH(K6,$B$2:$F$2,)))

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

    Re: Formula required for return a section wise different objects number of quantities.

    K7=SUMPRODUCT($A$1:$F$23,(INDEX($A$1:$F$23,MATCH(LOOKUP("zzzz",$K$3:K$3),$A$1:$A$23,0)+1,)=K6)*ISNUMBER(SEARCH($A$1:$A$23,$J7))*(LOOKUP(ROW($A$1:$A$23),ROW($A$1:$A$23)/ISNUMBER(SEARCH("Section-",$A$1:$A$23)),$A$1:$A$23)=LOOKUP("zzzz",$K$3:K$3)))
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Try the above, copy and paste towards right
    Last edited by samba_ravi; 11-12-2020 at 06:40 AM.
    Samba

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

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

    Re: Formula required for return a section wise different objects number of quantities.

    Thanks to all of you for your great formula solutions.

+ 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] Formula Help - A specific Part number, sum it's quantities, and multiply by 25.
    By HayHay in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-12-2020, 10:39 AM
  2. [SOLVED] Section wise summarize the data into one sheet from other work sheets
    By Mohammad Munawar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-21-2020, 09:25 AM
  3. Tally number wise required sum of hours,amounts,count of strength as per categories.
    By Mohammad Munawar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-01-2020, 07:13 AM
  4. [SOLVED] Formula to add up quantities depending on number of weeks
    By kirval in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2017, 01:27 PM
  5. Add up order quantities and show date required summary
    By miked79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-04-2017, 10:02 AM
  6. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  7. Replies: 2
    Last Post: 11-07-2005, 05:41 AM

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