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
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.
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:
Average cells based on multiple criteria with Averageifs function
If you want to calculate average with multiple criteria, the Averageifs function may help you.
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:
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:B19and 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:
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. 60-day money back guarantee.
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?
VBA code to average one or more ranges of nonzero numbers:
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
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.
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!
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