Averaging with IFs

J

John

I am trying to average data if it matches a criteria and if its greater than 1.


A B C

Team 95 86
Group 87 76
Example 81 99
Help 15 0
Team 86 94


What I want to do is add up all of the numbers from columns B and C if they
match a requirement from column A. So it would be like
"SUMIF(QA!C3:O252,A3,QA!D3:O252)". That includes the actual data set I want
to count and criteria. This part is easy. Next I want to divide that total to
find the average but only if the cell again matches a criteria and is greater
than 1. So it would be similar to a countif with multiple "IFs". If it
matches "A3" and its great than 1. The goal is find the average score for a
team. So in the example above I want the average for "Team."


My actual data set is QA!C3:O252. Column "C" is the column with team names
in it. The other columns contain either values or "0". Hopefully someone can
solve my issue. Thanks All
 
L

Luke M

Use this array** formula:

=AVERAGE(IF(QA!C3:C252=A3,IF(QA!B3:O252>1,B3:O252)))

**Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

countifs Function not giving desired result with date criteria 2
averaging a range 3
averaging multiple items from one list 2
Cell data entering slow! 0
Sumproduct in matrix means 3
Average IFS (Multiple Criteria) 0
Need help calculating probabilities 7
Averaging a colume of values. 1

Top