Skip to main content

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

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?

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

kte tab 201905

60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 

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! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 
 
Comments (16)
No ratings yet. Be the first to rate!
Sally
Sally
This comment was minimized by the moderator on the site
Hi, I'm trying to create an average only if the row meets criteria in other columns. I need to only include the number in column J if that row has a "B" in column F and a "M" in column E. I've worked out the formula if it's only one condition:

=AVERAGEIF(F2:F114, "B", J2:J114)

But I can't work out how to get it to only average numbers in column J that meet both conditions?
Reply
0
0
skyyang
skyyang     Sally
This comment was minimized by the moderator on the site
Hello, Sally
To calculate the average based on multiple criteria, you can apply the AVERAGEIFS function, please use the below formula:
=AVERAGEIFS(J2:J114, F2:F114, "B", E2:E114, "M" )

Please have a try, hope it can help you!
Reply
Report
0
0
Sally
Sally     skyyang
This comment was minimized by the moderator on the site
Thanks so much, this worked perfectly!
Reply
0
0
TFkidd
TFkidd
This comment was minimized by the moderator on the site
What formula would I use if I wanted to get my averages for a month but not count the days that are zero (0)? I am keeping track of my monthly electric usage but need to get an average even before the end of the months. Any ideas or help?
Reply
0
0
skyyang
skyyang     TFkidd
This comment was minimized by the moderator on the site
Hello, TFkidd
To solve your problem, please apply the below formula:
=AVERAGE(IF(B2:B31<>0,B2:B31))
Please try, hope it can help you!
Reply
Report
0
0
TRACY
TRACY
This comment was minimized by the moderator on the site
Hi there
I am trying to calculate percentage of people from across 4 regions (entered in column b)
and percentage by ethnicity (entered in column D)
I can calculate the average age and percentage of ages, but I can't seem to find a formula to calculate the regions and ethnicity. Any suggestions would be appreciated, thanks Tracy
Reply
0
0
Jenin J
Jenin J
This comment was minimized by the moderator on the site
To get an average of data in a column with multiple criteria the following formulae may be used.

Syntax = AVERAGEIFS (Range, Range 1, criteria 1, range 2, criteria 2)

In this case, my requirement is to find the average of the data in a range of numbers, without considering '0 - Zero' & values greater that '3000'.

Total data in the range is 31 nos ,

And the formulae will be

=AVERAGEIFS(G5:G35,G5:G35, "> 3000", G5:G35,"<>0")
Reply
0
0
La
La
This comment was minimized by the moderator on the site
I have 4 variables: 1st: Date of onset, 2nd: Date of termination, 3rd: Date of measurement, 4th: measurement. I want to calculate the average of the 4th variable between each period of onset-termination. How can i do it? I think its a bit tricky. Each date of onset has an counterpart date of termination. The same applies for the 3rd and 4th variable.
How can i make this happen?
Reply
0
0
Mark Main
Mark Main
This comment was minimized by the moderator on the site
VBA code to average one or more ranges of nonzero numbers:


Option Explicit
Function avgNonZeros(ParamArray rangeList() As Variant) As Variant
'Returns the average for all nonzeros of rangeList.
'rangeList may be one or multiple ranges.
Dim cell As Range
Dim i As Long
Dim totSum As Long
Dim cnt As Long
DoEvents 'allows calculations prior to performing
avgNonZeros = 0 'default return
For i = LBound(rangeList) To UBound(rangeList)
For Each cell In rangeList(i)
If cell <> 0 Then
totSum = totSum + cell
cnt = cnt + 1
End If
Next cell
Next i
If cnt <> 0 Then avgNonZeros = totSum / cnt
End Function
Reply
0
0
Lutfur
Lutfur
This comment was minimized by the moderator on the site
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.
Reply
0
0
Crist
Crist
This comment was minimized by the moderator on the site
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?
Reply
0
0
skyyang
skyyang     Crist
This comment was minimized by the moderator on the site
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!
View Attachments (1 / 5)
Reply
Report
0
0
pablo
pablo
This comment was minimized by the moderator on the site
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.
Reply
0
0
Stephanie
Stephanie
This comment was minimized by the moderator on the site
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!
Reply
0
0
ch
ch
This comment was minimized by the moderator on the site
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.
Reply
0
0
Jackie
Jackie
This comment was minimized by the moderator on the site
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
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations