+ Reply to Thread
Results 1 to 11 of 11

AverageIf function with multiple criteria (searching for text strings)using OR logic

  1. #1
    Registered User
    Join Date
    03-19-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    AverageIf function with multiple criteria (searching for text strings)using OR logic

    Dear all,

    I'm sorry if this is any way wrong or incorrect or if I am unknowingly breaking rules, as this is my first post.
    However, I really need help with a problem I have been stuck on for over a week and I simply cannot find a solution.
    The issue is really complicated and no matter how many different ways of doing each individual task element of it, I simply am not able to compile it into a working formula.

    It's quite a long explanation of the problem, so please do bear with me.

    First off, this problem is based on two data tables.
    1) The first is a 6 column by 300 row data table of which the 4th and 6th columns will be required in this formula.
    2) The second is an 11 column by 40,000 row data table, in which we will be concerned with all columns.

    The two tables are on separate pages (when I finish the whole project they will all be integrated into one page), with the former on Sheet1 and the latter on Sheet2

    The key is to find the average on an array of values based on criteria. The criteria is checking whether the the 4th column in the former table contains one of ten text strings. It need not have all ten text strings, simply must have one or more of the ten text strings within it.

    The ten text strings are individually calculated in the first ten columns of the latter table, and this formula is entered on the 11th column. So for example, A1:J1 will contain individual text strings, e.g "La" or something similar.

    What we are trying to do is find out which of the rows in the fourth column of the former table has one or more of the ten text strings within, and then to create an average of all the figures in the corresponding rows of the sixth column.

    So if La can be found in the fifth, tenth and eleventh rows, I want to find that out and then average out the values in the sixth column of the former data table at the fifth, tenth and eleventh rows.

    Currently my formula looks a bit like this, but it's not working, it always returns a #DIV/0 error.
    {=AVERAGE(IF(ISNUMBER(MATCH(Sheet1!$D$1:$D$300,{"*"&$A1&"*","*"&$B1&"*","*"&$C1&"*","*"&$D1&"*","*"&$E1&"*","*"&$F1&"*","*"&$G1&"*","*"&$H1&"*","*"&$I1&"*","*"&$J1&"*",},0))="TRUE",Sheet1!$E$1:$E$300))}

    Thank you in advance for all and any help!

    Martins1

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Excel shouldn't even accept that formula, ranges inside array constants don't compile.

    See if either of these gives you the expected results,

    {=SUM(IFERROR(AVERAGEIF(Sheet1!$D$1:$D$300,"*"&$A1:$J1&"*",Sheet1!$E$1:$E$300),0))}

    or

    {=IFERROR(SUMIF(Sheet1!$D$1:$D$300,"*"&$A1:$J1&"*",Sheet1!$E$1:$E$300)/COUNTIF(Sheet1!$D$1:$D$300,"*"&$A1:$J1&"*"),0)}

  3. #3
    Registered User
    Join Date
    03-19-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Thank you very, very much! It worked

  4. #4
    Registered User
    Join Date
    03-19-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Quick second question:
    I would like to do a countif statement with both AND and OR logic:
    Basically, there are 12 criteria:
    If the criteria range meets:
    Criteria 1
    Criteria 2
    1 or more of Criteria 3-12
    Then i would like to count it. How do i do this?

    Thank you!

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Something like

    =SUMPRODUCT((Range1=Crit1)*(Range2=Crit2)*((((Range3=Crit3)+(Range4=Crit4)+(Range5=Crit5))>0))

    Basically, you multiply for AND, you add for OR.

    The >0 test at the end of the OR section is to prevent rows being counted multiple times when more than one of the OR criteria is met.

    If the results come out wrong, check the parenthesis, if any are missing or misplaced it can mess things up.

  6. #6
    Registered User
    Join Date
    03-19-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Thanks, and if the criteria were the same but I wanted to do a sumifs version of that?

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Same principle, just tag the sum range onto the end

    =SUMPRODUCT((Range1=Crit1)*(Range2=Crit2)*(((Range3=Crit3)+(Range4=Crit4)+(Range5=Crit5))>0)*SumRange)

    Also, noting my own advice on misplaced parenthesis, I had too many opening parenthesis in the last example it should have been

    =SUMPRODUCT((Range1=Crit1)*(Range2=Crit2)*(((Range3=Crit3)+(Range4=Crit4)+(Range5=Crit5))>0))

  8. #8
    Registered User
    Join Date
    03-19-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Thank you very much!
    In terms of criteria, how would you search if the criteria range(B3:B10) contains a certain substring anywhere within it, in which that certain substring is determined by what is in another cell (L2).
    Currently I'm using, for example (B3:B10="*"&L2&"*")
    Thanks

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    For that you would need to use a couple of extra functions, unlike sumif or countif, sumproduct doesn't accept wildcards.

    ISNUMBER(SEARCH(L2,B3:B10))

    Depending on requirements, you could use SEARCH or FIND, the only difference being, FIND is case sensitive, where SEARCH is not.

    One thing to note, this method is only reliable when you require a * wildcard at both ends of the criteria, searches with a single * wildcard, or any ? single character wildcards can return false positives.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,372

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Try

    =IF(SUM(--(ISNUMBER(SEARCH(L2,$B$3:$B$10,1)))),"Y","N")

    Enter with Ctrl+Shift+Enter

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

    Quote Originally Posted by Martins1 View Post
    Currently I'm using, for example (B3:B10="*"&L2&"*")
    If that's all the formula is doing you can use COUNTIF:

    =COUNTIF(B3:B10,"*"&L2&"*")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Replies: 7
    Last Post: 12-14-2015, 01:20 PM
  2. [SOLVED] Searching for multiple text strings in conditional formatting
    By toubab in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-05-2015, 01:12 AM
  3. Searching multiple text strings/variables
    By steve61 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2014, 07:58 AM
  4. [SOLVED] Searching for multiple text strings
    By kschrieb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-12-2014, 08:38 PM
  5. [SOLVED] Formula for searching multiple text strings
    By Excelcious in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2014, 06:29 PM
  6. Help searching an array of text strings for common strings
    By ABComp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-23-2012, 11:19 PM
  7. searching for multiple text strings
    By eddie in forum Excel General
    Replies: 4
    Last Post: 04-10-2005, 06:06 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