+ Reply to Thread
Results 1 to 10 of 10

Extract values based on two (2) criteria

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Extract values based on two (2) criteria

    Need a formula to extract values in column A through D if criteria is met (2 criteria). There is a List Sheet that houses the names and state. Actual data is in Data sheet. Headings are Sales Person, Region, Products and Price. Would like a formula to extract the right values when a sales person (in G2) is selected from the dropdown and the state selected (in H2). For example, G2: Vergie, Nicholes and H2: Indiana.

    Also, need a second formula to extract unique names and the average sales price (see desired outcome in M4:N9.

    See attached file.

    Sample I
    Sales Person Region Products Price
    Vergie, Nicholes * North Peanuts $25
    Vergie, Nicholes * North Peanuts $41
    Vergie, Nicholes * North Peanuts $13
    Vergie, Nicholes * North Peanuts $15
    Vergie, Nicholes * North Peanuts $32
    Vergie, Nicholes * North Peanuts $38
    Vergie, Nicholes * North Peanuts $11
    Vergie, Nicholes * North Peanuts $52
    Vergie, Nicholes * North Peanuts $33
    Vergie, Nicholes * East Peanuts $40
    Vergie, Nicholes * South Peanuts $29
    Vergie, Nicholes * South Peanuts $7
    Vergie, Nicholes * South Peanuts $39
    Vergie, Nicholes * South Peanuts $2
    Vergie, Nicholes * South Peanuts $53


    Sample II
    Sales Person Avg. Sales
    Anisha, Frum * $13
    Dedra, Irving * $43
    Eugena, Mims * $47
    Illa, Rhem * $51
    Vergie, Nicholes * $29
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,652

    Re: Extract values based on two (2) criteria

    You say state but you show region? State is not available in "Data".

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Extract values based on two (2) criteria

    In G5
    =IF(ROWS($A$1:$A1)>COUNTIF($A$2:$A$79,$G$2),"",INDEX(A$2:A$79,SMALL(INDEX(($A$2:$A$79=$G$2)*(ROW($A$2:$A$79)-ROW($A$2)+1),),COUNTIF($A$2:$A$79,"<>"&$G$2)+ROWS($A$1:$A1))))
    Copy down and across as far as you need

    In M5
    =IF(ROWS($A$1:$A1)>COUNTIF(State,$H$2),"",INDEX(List,SMALL(INDEX((State=$H$2)*(ROW(State)-ROW($A$2)+1),),COUNTIF(State,"<>"&$H$2)+ROWS($A$1:$A1))))

    In N5
    =IF(M5="","",IFERROR(AVERAGEIF($A$2:$A$79,M5,$D$2:$D$79),0))
    Copy down as far as you need
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,652

    Re: Extract values based on two (2) criteria

    In G5 copy across and down


    =IFERROR(INDEX(A$2:A$79,SMALL(IF($A$2:$A$79=$G$2,ROW(Data!$A$2:$A$78)-ROW(Data!$A$2)+1,""),ROWS(Data!$A$2:A2))),"")

    Enter with Ctrl+Shift+Enter

    in M5

    =IFERROR(INDEX(List!$A$2:$A$21,SMALL(IF(State=Data!$H$2,ROW($A$2:$A$21)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

    Enter with Ctrl+Shift+Enter



    in N5

    =IFERROR(AVERAGEIF($A$2:$A$79,$M5,$D$2:$D$79),"")

    Copy down

    See attached
    Attached Files Attached Files
    Last edited by JohnTopley; 09-07-2015 at 04:11 PM.

  5. #5
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    Ace_XL: solution works but the state dropdown (H2)and Salesperson dropdown in G2 criteria are only extracting values in A:E. When both dropdowns are selected, the values should be extracted according. Only the Salesperson dropdown is working. Have added state in column E for the pull. See attached file. a new dropdown has been added in M2 for the average. M2 dropdwon should be used to extract the unique names and associated values. See attached file.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,652

    Re: Extract values based on two (2) criteria

    State was not present in your first file! Only required for table 2: You are confusing matters!
    Last edited by JohnTopley; 09-07-2015 at 04:29 PM.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,652

    Re: Extract values based on two (2) criteria

    To select by state (Not included in your first file!)

    =IFERROR(INDEX(A$2:A$79,SMALL(IF(($A$2:$A$79=$G$2)*($E$2:$E$79=$H$2),ROW(Data!$A$2:$A$78)-ROW(Data!$A$2)+1,""),ROWS(Data!$A$2:A2))),"")
    Last edited by JohnTopley; 09-07-2015 at 04:33 PM.

  8. #8
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    JohnTopley: sorry for the confusion. I added state after I realized it is need for the two dropdown options to work. Thanks

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,652

    Re: Extract values based on two (2) criteria

    You only need to put State in H2: it is redundant in M2.

    Have we provided the solution you require: if so can you mark the thread as solved.

    Thank you.

  10. #10
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    JohnTopley: works like a charm. Thanks a million JohnTopley.

+ 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] Extract values based on three (3) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-23-2015, 02:30 PM
  2. Extract values based on two (2) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-19-2015, 04:14 AM
  3. [SOLVED] Extract values based on two (2) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-18-2015, 05:10 AM
  4. [SOLVED] Extract duplicated values based on an extra criteria
    By Eduard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2013, 05:50 AM
  5. Replies: 1
    Last Post: 02-14-2013, 02:32 PM
  6. [SOLVED] Extract unique values out of list based on an extra criteria
    By sven1975 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 05:02 AM
  7. Extract Unique Values from an Array based on Criteria
    By ronleex324 in forum Excel General
    Replies: 1
    Last Post: 10-04-2011, 06:37 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