Solved
MS Excel 2010 Average by group in a range
Posted on 2011-10-10
HI All -
I'm trying to count up the number of cells that meet a certain criteria identified in another column and I'm having issues.
Here's what my spreadsheet looks like
Category Score1 Score2 Score3
A 4 3 7
A 4 3 7
B 2 7
B 4 3 7
D 4 3 7
E 1 7
F 7
A 4 3 7
C 4 3 7
C 4 3 7
(The scores are all different on the sheet .. but this illustrates the example.)
Not all of the scores will be filled in for that category and that's ok - but what I do want is an *average* of each score across the board.
So I want like:
Average of A: 4.35
Average of B: 7
etc
I'm pretty sure it can be done in just one formula (copied down for each line to read in the new parameter of the value to average from) - I really don't understand it, but would I need to use SUMPRODUCT here?
I'm familiar with the COUNTIF(S)/AVERAGEIF(S)/SUMIF(S) functions..would a combination of those work?
Here's what I've been using and doesn't work..
=SUMIFS($I$11:$M$51, $F$11:$F$51, $C$2) / COUNTIFS(I11:M51, $F$11:$F$51 & "=" & $C$2)
The SUMIFS part works to gathered the ranged sum I want - but the COUNTIFS part is just returning 0.
Any help would be much appreciated! This is Excel 2010.
Thanks!