Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to average cells based on multiple criteria in Excel?

In Excel, most of us may be familiar with COUNTIF and SUMIF functions, they can help us to count or sum values based on criteria. But have you ever tried to calculate the average of values based on one or more criteria in Excel?

Average cells based on one criterion with Averageif function

Average cells based on multiple criteria with Averageifs function


Average / Sum cells based on criteria in another column:

With Kutools for Excel’s Advanced Combine Rows utility, you can quickly sum, average, count the cells based on the same values in another column.

doc-avergae-with-criteria-5


arrow blue right bubble Average cells based on one criterion with Averageif function


Supposing, I have the following data range, column A is the list of product and column B is the orders, now, I will average the order cells where the product is KTE.

doc-avergae-with-criteria-1

Please enter this formula into your desired cell: =AVERAGEIF(A2:A19,D2,B2:B19), (A2:A19 is the data which contains the criteria, B2:B19 refers to the range you want to average and D2 is the criterion you want to average the values based on), and press Enter key to get the result, see screenshot:

doc-avergae-with-criteria-2


arrow blue right bubble Average cells based on multiple criteria with Averageifs function

If you want to calculate average with multiple criteria, the Averageifs function may help you.

The Averageifs syntax as follows:

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

  • Average_range: is the range of cells to average;
  • Criteria_range1, criteria_range2, … are the ranges in which to evaluate the associated criteria;
  • Criteria1, criteria2, … are criteria which you are based on.

Take the above data for example, now, I want to average the order of KTE and the order greater than 500. Please do as this:

Enter this formula into a blank cell: =AVERAGEIFS(B2:B19,A2:A19,E1,B2:B19,">500")( A2:A19 is the data which contains the criteria1, B2:B19 refers to the range you want to average, E1 and >500 are the criteria1 and criteria 2), then press Enter key to get the result you want. See screenshot:

doc-avergae-with-criteria-3

Note: If you have more than two criteria that you want, you just need to add the criteria ranges and criteria you need as this: =AVERAGEIFS(C2:C19, A2:A19, F1, B2:B19, F2, C2:C19, "<500"), (A2:A19 and F1 are the first criteria range and criteria, B2:B19 and F2 are the second criteria range and criteria, C2:C19 and < 500 are the third criteria range and criteria, C2:C19 refers to the range you want to average the values), see screenshot:

doc-avergae-with-criteria-4


Related articles:

How to average top or bottom 3 values in Excel?

How to average last 5 values of a column as new numbers entering?

How to average every 5 rows or columns in Excel?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Lutfur · 8 days ago
    Hi,
    I have 200 observations. I need to get an average of 21st to 40th observations. I am struggling to set an averageif formula. Any suggestion will be appreciated.
  • To post as a guest, your comment is unpublished.
    Crist · 2 months ago
    I have a similar problem like example #3, but the difference is that I need the average between two dates instead of for a single month. Any suggestion?
    • To post as a guest, your comment is unpublished.
      skyyang · 2 months ago
      Hello, Crist,

      To solve your problem, please apply the below formula:
      =AVERAGEIFS(C2:C15, A2:A15, F1, B2:B15, ">=" &F2, B2:B15, "<=" &G2, C2:C15, ">300")

      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    pablo · 1 years ago
    Hi there,


    My is Pablo and I would like to ask you about this situation. I have got a column with several values and some of them are zeros. As they are dB measurements this is the array formula I use to get the average: =10*LOG(AVERAGE(10^(C3:C66/10)))

    My problem is that I am trying to get with a formula that does not take in account the zeros.

    I have tried the next formula but it seems that does not work for my situation: =10*LOG(AVERAGEif(C3:C66,"<>0",[10^(C3:C66/10)]))

    It would be very apprecited if you could give me a hint to solve this problem.

    Thank you in advance,

    Pablo.
  • To post as a guest, your comment is unpublished.
    Stephanie · 1 years ago
    I want to take numbers in multiple columns and combine them into one average. I have three columns to average, I want two of the columns to count for 40% each and the remaining column to count for 20%. Is this possible? Thanks!
  • To post as a guest, your comment is unpublished.
    ch · 2 years ago
    I want to average cells is those cells are between a range.
    If A is between range 1 & range 2, average value in B. What formula would I use?
    Ex.
    A B Range 1 Range 2
    $78,093 651,462 55001 60000
    $76,968 636,679
    $76,946 1,682,295
    $73,909 142,277
    $73,567 624,257
    $71,505 492,828
    $67,772 351,316
    $63,786 458,169
    $63,263 157,665
    $58,427 180,498
    $56,693 187,028
    $56,510 73,339
    $56,129 80,832

    Thanks for any help.
  • To post as a guest, your comment is unpublished.
    Jackie · 2 years ago
    7
    $3.00
    20
    $40.00
    3
    $6.00
    0
    $-
    MY AVERAGE FOR CELL B3,B5,B7,B9 SHOULD BE 10
    MY COUNT FOR CELL B3,B5,B7,B9 SHOULD BE 3

    How do I get averages for specific cells excluding zero