+ Reply to Thread
Results 1 to 9 of 9

Formula to pull data when multiple criteria are met.

  1. #1
    Registered User
    Join Date
    08-21-2015
    Location
    calgary,canada
    MS-Off Ver
    2010
    Posts
    66

    Formula to pull data when multiple criteria are met.

    Good Day all,

    I have a formula that pulls data when a specific criteria is met,

    however i would like to modify the formula to pull data if 2 criteria are met.

    here is what i havew now:=IFERROR(1/(1/SUMIFS('MATERIAL DATA'!$K$5:$K$1994,'MATERIAL DATA'!$L$5:$L$1994,BOM!$K5)),"")

    so this asks sum sheet called material data k5 to 5 1994 if sheet called material data l5 to I1994 matches sheet called BOM k5

    this works perfecty however i would like to add a second match . and i am not sure where i would put it in the formula to have it work.

    the added part to the formula is: also search sheet called material data in cells w5:w1994 for "MF"or"CS" if the search matches MF then sum only the ones that match MF if the search matches CS then sum only the ones that match CS.

    i hope ive made it clear enough.

    if not let me know

    thanks for any help

    mike rand

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Formula to pull data when multiple criteria are met.

    What if there is "MF" and "CS" in column W?

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    08-21-2015
    Location
    calgary,canada
    MS-Off Ver
    2010
    Posts
    66

    Re: Formula to pull data when multiple criteria are met.

    hello,

    attached is the excel book in question

    note: sheet BOM in blue needs to calculate only if sheet material data cell w4:w1994 contains MF as well as the other
    criteria in the formula

    note: sheet BOM in yellow needs to calculate only if sheet material data cell w4:w1994 contains CS as well as the other criteria in the formula


    thanks
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Formula to pull data when multiple criteria are met.

    That's not a small representative sample and the desired results are not there...

    Also, these do not have to be entered as array formulas.

    Try these:

    L4 =IFERROR(1/(1/SUMIFS('MATERIAL DATA'!K:K,'MATERIAL DATA'!L:L,K4,'MATERIAL DATA'!W:W,"MF")),"")

    AJ4 =IFERROR(1/(1/SUMIFS('MATERIAL DATA'!K:K,'MATERIAL DATA'!L:L,K4,'MATERIAL DATA'!W:W,"CS")),"")

  5. #5
    Registered User
    Join Date
    08-21-2015
    Location
    calgary,canada
    MS-Off Ver
    2010
    Posts
    66

    Re: Formula to pull data when multiple criteria are met.

    Yes your right ,

    however i tried to make it smaller but each time i did it stopped creating data and all the squares went to errors so there would have been no data for you to see , sorry


    thanks i will try those formulas and omit the control shift enter

    thanks again
    i will let you know how it turned out or if i have any problems.

    mike rand

  6. #6
    Registered User
    Join Date
    08-21-2015
    Location
    calgary,canada
    MS-Off Ver
    2010
    Posts
    66

    Re: Formula to pull data when multiple criteria are met.

    Falcondude,



    PEFECT


    THANKS SO MUCH,


    works lieka charm.

    mike rand

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Formula to pull data when multiple criteria are met.

    Happy to help. Thanks for the rep!

  8. #8
    Registered User
    Join Date
    08-21-2015
    Location
    calgary,canada
    MS-Off Ver
    2010
    Posts
    66

    Re: Formula to pull data when multiple criteria are met.

    Falcondude,

    I am having troubles converting the formula for the GORES section on the BOM sheet

    formula you modified works perfect for all other but i can seem to get the right modification for the gores.

    you will see on BOM cell at6 i have this formula,

    =IFERROR(INDEX('MATERIAL DATA'!$G$5:$G$1994,SMALL(IF('MATERIAL DATA'!$T$5:$T$1994="GORE",ROW('MATERIAL DATA'!$T$5:$T$1994)),ROWS($1:3))),"")

    i need it to only work if 'MATERIAL DATA'!$T$5:$T$1994="GORE" is correct and if 'MATERIAL DATA'!$q$5:$q$1994="ss" is correct,

    can you help me out on this as well.

    thanks again

    mike rand

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Formula to pull data when multiple criteria are met.

    That is not the formula that you have in BOM!AT6. That is the formula that you have in BOM!AT7.

    You can use this in AT6:

    =IFERROR(INDEX('MATERIAL DATA'!G:G,SMALL(IF(('MATERIAL DATA'!Q$5:Q$1994="ss")*('MATERIAL DATA'!T$5:T$1994="GORE"),ROW('MATERIAL DATA'!$T$5:$T$1994)),ROWS($1:1))),"") Ctrl Shift Enter

+ 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] Pull data from a table based on multiple criteria - old formula trying to use AND
    By tweaver in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-12-2016, 04:37 PM
  2. Pull text data using multiple criteria
    By George Holmer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-13-2015, 10:21 AM
  3. Pull data from multiple sheets to one sheet if criteria met
    By Ju1cy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-16-2014, 08:28 AM
  4. [SOLVED] Pull data from one sheet to another based on multiple criteria
    By thestalkycop in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2014, 01:38 AM
  5. Pull data from one sheet to another using multiple criteria
    By Farhaad Saleh in forum Excel General
    Replies: 5
    Last Post: 07-22-2011, 10:32 AM
  6. Replies: 5
    Last Post: 01-05-2011, 03:13 PM
  7. Referencing multiple criteria to pull data
    By doug1 in forum Excel General
    Replies: 2
    Last Post: 03-14-2006, 05:30 PM

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