How to calculate average in a column based on criteria in another column in Excel?
For calculating average in a column based on same values in another column (see screenshot below), what can you do? In this article, we will show you two methods to get it down in details.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
You can calculate average in a column based on same value in another column with formulas. Please do as follows.
1. Select a blank cell, enter formula =AVERAGEIF(B2:B13,"Owenton",C2:C13) into the formula bar, and then press the Enter key. See screenshot:
Note: in the formula, B2:B13 is the range contains the criteria, C2:C13 is the range contains the data you want to calculate average, “Owenton” is the value you will calculate based on. You can change them as you need.
Now the sold average of “Owenton” is calculated. For calculating the average of other values in column B, you need to repeat the above operation until all value average are calculated.
This section will show you how to calculate all average in a column based on same value in another column at once in Excel.
1. Select the range, and then click Kutools > Content > Advanced Combined Rows. See screenshot:
2. In the Combine Rows Based on Column dialog box, select the criteria column, and then click the Primary Key button, select the data column you want to calculate average, and then click Calculate > Average. And finally click the OK button. See screenshot:
Note: For the left column in selected range, please specify combine criteria for them. In this case, I combine the Date column with comma.
Now the averages in column C are calculated based on the same values in column B. see screenshot:
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!
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.· 2 years agoThank you for this information. Question, how would I do this formula if the criteria in B is based on a long list, so I can't enter each individual name into the formula? I just need it to average based on each one of many names?