+ Reply to Thread
Results 1 to 7 of 7

Formula to return range based on criteria

  1. #1
    Registered User
    Join Date
    03-15-2013
    Location
    New York
    MS-Off Ver
    Office 365 MSO
    Posts
    5

    Formula to return range based on criteria

    Hi All

    I am trying to figure out a formula that will return a range based on specific criteria. For example:

    Criteria 1: Column A, Criteria 2: Column B. When both a fulfilled return A1:A15. I've attached a basic example, looking for a formula to return the range under the red column. Example 1.xlsx

    Appreciate any help and thank you so much for your time and energy!

    Edit: Using Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20806) 64-bit if it matters

    Edit 2: I updated the attachment with the exact details I am trying to work on, there is a new tab "Specific Example" which *crossed fingers* I have completely made generic. The focus is in rows 4 through 9, the "Median", "Fit range" and "Pop" columns. I have currently performed the range selections for each of them manually but I would like to automate the range selection in the median, countifs and count formula so that people can mess with the underlying population and the calculations update automatically
    Attached Files Attached Files
    Last edited by wd56; 11-29-2023 at 12:22 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    10-17-2007
    Location
    Tasmania, Australia
    MS-Off Ver
    2019, 365(v2403)
    Posts
    370

    Re: Formula to return range based on criteria

    In Cell C3
    Please Login or Register  to view this content.
    In cell C4
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-15-2013
    Location
    New York
    MS-Off Ver
    Office 365 MSO
    Posts
    5

    Re: Formula to return range based on criteria

    Thank you russkris for taking the time to respond. The specific reason I wanted a formula to return the range, was because I want to use the median formula on the range, and then calculate how many items fall within 20% of the median. I am currently populating the range in the median and countifs formula manually but wanted to automate the range calculation. I plan to hand the file over to someone who isnt proficient with excel and if they added rows in the population it would automatically update all the calculations.
    Last edited by wd56; 11-29-2023 at 12:07 AM.

  4. #4
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,971

    Re: Formula to return range based on criteria

    Try this in C4 and copy down.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-15-2013
    Location
    New York
    MS-Off Ver
    Office 365 MSO
    Posts
    5

    Re: Formula to return range based on criteria

    Omg that's awesome, thank you. I should be able to replicate the filter formula for the range sections in the countifs formula as well right?

    Edit: not sure why i keep getting an excel error "There is a problem with this formula. Not trying to type formula.....etc"

    I've always experienced this when my brackets are generally wrong. Doesnt seem to be here. trying to replicate the filter formula in the countifs formula in cell F4 "=COUNTIFS(FILTER($C$12:$C$171,(A4=$A$12:$A$171)*(B4=$B$12:$B$171))},">="&D4,FILTER($C$12:$C$171,(A4=$A$12:$A$171)*(B4=$B$12:$B$171)),"<="&E4)"
    Last edited by wd56; 11-29-2023 at 05:05 AM.

  6. #6
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,971

    Re: Formula to return range based on criteria

    Countifs can't use dynamic array. Use this

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    2,008

    Re: Formula to return range based on criteria

    C3=COUNTIFS($A$8:$A$200,A3,$B$8:$B$200,B3,$C$8:$C$200,">="&AGGREGATE(16,6,$C$8:$C$200/($A$8:$A$200=A3)/($B$8:$B$200=B3),0.5)*0.2)

    Copy dowm

+ 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 to return value based on multiple criteria including specific date range
    By ShellBeatz in forum Outlook Formatting & Functions
    Replies: 7
    Last Post: 04-12-2022, 07:56 AM
  2. [SOLVED] How to return a range of values based on Multiple Criteria
    By Jamster in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-28-2020, 10:16 AM
  3. [SOLVED] Return Value based on Range Criteria selected
    By vill in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-06-2018, 12:10 PM
  4. [SOLVED] Return first number in Range based on multiple criteria
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2015, 09:35 AM
  5. Formula to return values based on criteria and date range
    By jr217 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2014, 06:20 AM
  6. [SOLVED] Return a number from a range of cells based on criteria?
    By Polymorpher in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-25-2014, 11:01 AM
  7. return all range based on criteria
    By Dumy in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-28-2011, 04:21 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