How can i make this happen?
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 / 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.
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:
If you want to calculate average with multiple criteria, the Averageifs function may help you.
The Averageifs syntax as follows:
- 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:
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:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 23 days agoI 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?
- To post as a guest, your comment is unpublished.· 28 days agoVBA 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
If cnt <> 0 Then avgNonZeros = totSum / cnt
- To post as a guest, your comment is unpublished.· 4 months agoHi,
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.· 6 months agoI 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.· 6 months agoHello, 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.· 1 years agoHi 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,
- To post as a guest, your comment is unpublished.· 2 years agoI 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.· 3 years agoI 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?
A B Range 1 Range 2
$78,093 651,462 55001 60000
Thanks for any help.
- To post as a guest, your comment is unpublished.· 3 years ago7
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