+ Reply to Thread
Results 1 to 3 of 3

AVERAGEIFS() with criteria that a string NOT include specific text

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    Place, United States
    MS-Off Ver
    Excel 2003
    Posts
    12

    AVERAGEIFS() with criteria that a string NOT include specific text

    I'm trying to find the average of a column where two conditions are met by using the AVERAGEIFS() function. One of these conditions is that the cell within the 'criteria' range NOT contain a substring. In the example file I've included I want the average of the Value column, but only including rows where the 'Key' doesn't contain the letter b and 'Level' is 1. I tried to do this using the function:

    =AVERAGEIFS(A2:A32,B2:B32,<>"*b*",C2:C32,1)

    This gives an error because I believe <> is expecting a numeric value and I give it string criteria. Is it possible to get this to work using AVERAGEIFS or another method?

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: AVERAGEIFS() with criteria that a string NOT include specific text

    A small correction in your original formula. Change <>"*b*" to "<>*b*"

    =AVERAGEIFS(A2:A32,B2:B32,"<>*b*",C2:C32,1)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

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

    Re: AVERAGEIFS() with criteria that a string NOT include specific text

    Try it like this...

    =AVERAGEIFS(A2:A32,B2:B32,"<>*b*",C2:C32,1)
    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. [SOLVED] Modifying AVERAGEIFS function to include certain data
    By Ezomz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-14-2013, 11:35 AM
  2. [SOLVED] Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)
    By thaphthia in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 04-11-2013, 05:22 PM
  3. Averageifs--multiple text criteria
    By Sshamash in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-19-2013, 09:41 AM
  4. Replies: 5
    Last Post: 02-04-2013, 05:32 PM
  5. [SOLVED] include string or text value in cell reference
    By nelly in forum Excel General
    Replies: 5
    Last Post: 06-06-2006, 07:40 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