Averaging with IFs
 

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

Averaging with IFs
 

Use this array** formula:

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

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

Luke M
*Remember to click "yes" if this post helped you!*


"John" wrote:

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

Averaging with IFs
 

Luke M...You the MAN!!! Worked like a dream!!!! Big Help!!!!

"Luke M" wrote:

Use this array** formula:

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

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

Luke M
*Remember to click "yes" if this post helped you!*


"John" wrote:

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