Combine Sumif and Countif

  • Thread starter Thread starter John
  • Start date Start date
J

John

I need to combine sumif and countif. I am trying to add up data in a an area
say D1:O256 if it matches a criteria. Thats easy. Sumif. Then I want to
divide that number and find the average of the sum. So I would sumif to get
the total and sumifcountif the same area if the cell is greater than 1. Kinda
confusing. Basic example below but I need to to be sum if and an additional
"if" in countif. So the countif needs to be if it matches the requirements
from the sumif. Need some help here. Probably an easy fix. Just lost

=(SUMIF(QA!C3:O252,A3,QA!D3:O252))/(COUNTIF(D3:O3,">1"))
 
Try this array* formula:

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

The ranges in your description vary, so I've assumed that you mean
C3:O252.

I'm not sure why your COUNTIF needs to be greater than 1 - is this
another condition, or did you mean <>0 ? AVERAGE does not count cells
which contain zero.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
need to amend the formula you must use CSE again to commit it.

Hope this helps.

Pete
 
hi
there doesn't seem to be anything wrong with your structure. i have use this
structure before and it works. the only odd think i noticed was that the
sumif is summing data on sheet QA where the criteria is on current sheet. and
the countif data is also on the current sheet. was this deliberate?

regards
FST1
 
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
 
Back
Top