+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    81

    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
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,840

    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



  3. #3
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    81

    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
    7,494

    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.

  5. #5
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    81

    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
    5,472

    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 2013
    Posts
    7,915

    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
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    81

    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