Excel Average Based on Multiple Criteria

In Excel, you can use the SUMIF and COUNTIF functions, to sum and count values, based on criteria. Did you know that you can also calculate an average, based on criteria?

Average with One Criterion

If you only have one criterion, you can use the AVERAGEIF function. In the screen shot below, the average quantity is calculated for any orders where Pens were sold.

=AVERAGEIF(A1:A10,"Pen",B1:B10)

averageif01

Average for Multiple Criteria

If you need multiple criteria for the average, and you’re using Excel 2007 or a later version, you can use the AVERAGEIFS function.

In the next screen shot, the AVERAGEIFS formula checks for Pen in column A, and a quantity greater than or equal to 10, in column B. The criteria values are entered on the worksheet – cells D3 and E3 – where they can be changed easily.

For the rows that meet those criteria – rows 6 and 10 -- an average quantity is calculated.

=AVERAGEIFS(B2:B10,A2:A10,D3,B2:B10,">="&E3)

averageifs01

Download the Sample File

To see these average formulas, and other examples, you can download the sample file from my Contextures website: Excel Average Formulas. The sample file is zipped, and is in xlsx format (Excel 2007 and later versions).

__________

13 comments to Excel Average Based on Multiple Criteria

  • Maxime M

    I just discovered that 2 hours before I read this post. Amazing!

  • Jason M

    Maybe I'm getting old, but an array formula seems to be easier to read, and therefore understand, than the AVERAGEIFS:

    =AVERAGE(IF((A2:A10=D3)*(B2:B10>=E3),B2:B10))

    But then again, the learning curve for AVERAGEIFS vs. arrays is probably quicker for an Excel newbie.

  • I never knew there is such a function. In such scenarios I also used the method that "Jason" has mentioned in the above comment.
    Thanks for sharing such a useful post.

  • Tamatam

    This Site is Very Useful. I learned a lot of Excel Techniques from this Website.

    Best Regards,
    TAMATAM

  • Ray

    Yes I am trying to figure out an "averageif function" off of more than one criteria in the same column if it is even possible. If column "A" has the criteria & column "b" has times how can I get the average time between say three criteria? Example: Criteria column A = Car 1, Car 2 Car 3 Car 4 Car 5 Column B = Time 11:49, 10:30, 9:55, 7:24, 11:34 I want the average time between Car 1, Car 3, Car 5 or should I be using a different function? Any help would be greatly appreciated!
    Thanks Ray

  • KH Chuah

    How do I get the average if I wanted averageif more than 1 item? Can i still do so and the display cell will actually clever enough to filter based on the item's name?

  • chandan

    i excel i have multiple qty like 10 12 13 14 8 9 i want to change this to single qty in row one by one any formula for this

  • Mark Ferdinando

    Hi

    I am trying to come up with an average score across multiple retail outlets however the difficulty I'm experiencing is because I have 16 disciplines and 15 of them are the higher the number the better whereas 1 of the disciplines is the lower the number the better.
    Can anyone help with how to set this formula.

    Thanks

  • Jess

    Thank you so much!! This helped me so much. :)

  • mel

    What useful information! I would like to make an easily-as-possible understood array function that can calculate summary statistics (average, max, min) for daily averages of hourly data, when there are different numbers of hours in each day (usually 15-24 hours in each day). I work for native american tribal environmental programs, who gather data logged hourly, but need daily averages without having to manually create averages for each day that does not include exactly 24 hours of data in each day. Can you recommend anything? thanks so much!

  • mel

    Thanks! For some reason, most people I work for hate pivot tables. I think I have found a solution that doesn't involve pivot tables, which is to first filter for unique values (dates) from the column of date-time, and then using that list of unique dates, create an array and within that an if-average function that looks row by row for the pollutant concentrations that are on those dates. Have you any suggestions on that? I also am working on getting people more comfortable with pivot tables, as they are so useful. Thank you for your help!

  • Mitch

    Can someone tell me how to average only the values between two other values? Such as, I want to only average numbers that fall between 0 and 8000 in a column. How would I do an AVERAGEIF function? I can do it with either less than 8000 or greater than 0 but not both.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>