[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# MS Excel 2010 Average by group in a range

Posted on 2011-10-10
Medium Priority
295 Views
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!
0
Question by:rmm2001
• 4
• 3
• 3
• +1

LVL 24

Expert Comment

ID: 36945509
Perhaps something like this, adjusting ranges as necessary. So A2:A11 is the one containing categories and B2:D11 contain the values. I don't get the figures you quote btw.

=AVERAGEIF(A2:A11,"A",B2:D11)
0

LVL 59

Expert Comment

ID: 36945523

Have you looked into option of creating pivot tables because it will be really easy to manipulate your data and do what you are looking with the help of pivot tables only..

Enclosed is the example for your reference with pivots and formulas...

Saurabh
Sample-Example.xlsx
0

LVL 50

Expert Comment

ID: 36945543
I don't think even the SUMIFS part of your formula will work - formulas like SUMIFS, COUNTIFS, AVERAGEIFS require all ranges to be the same size so you need another approach, either an array formula like this

=IFERROR(AVERAGE(IF(\$F\$11:\$F\$51=\$C\$2,IF(ISNUMBER(\$I\$11:\$M\$51),\$I\$11:\$M\$51))),0)

confirmed with CTRL+SHIFT+ENTER

or a non-array version with SUMPRODUCT

=SUMPRODUCT((\$F\$11:\$F\$51=\$C\$2)*\$I\$11:\$M\$51)/MAX(1,SUMPRODUCT((\$F\$11:\$F\$51=\$C\$2)*ISNUMBER(\$I\$11:\$M\$51)))

regards, barry
0

LVL 24

Expert Comment

ID: 36945549
Sorry, I've got that wrong, the AVERAGEIF doesn't seem to like multiple columns.
0

LVL 7

Author Comment

ID: 36945570
@saurabh726: Pivot table is a good idea...I'll see if I can add that one in. Thanks!

@StephenJR: =AVERAGEIF(A2:A11,"A",B2:D11) works awesome! One question about it though, does it toss out 0's (identified by empty cells) in the average or include them?
0

LVL 50

Expert Comment

ID: 36945575
See this sample sheet, both formulas I suggested are shown - they should give the same results

regards, barry
27389620.xls
0

LVL 24

Expert Comment

ID: 36945588
rmm2001 - I'm afraid it is just a coincidence that it works for A - fooled me too! See Barry's explanation.
0

LVL 50

Expert Comment

ID: 36945596
>=AVERAGEIF(A2:A11,"A",B2:D11) works awesome!

No, it doesn't work as you want it to - that formula only averages B2:B11 - values in columns C and D are ignored - that's why you need one of the approaches that I suggested

All SUMIF, SUMIFS, COUNTIFS, AVERAGEIF and AVERAGEIFS formulas only work with identical sized ranges. some don't give errors but they don't sum or average the cells you expect.

regards, barry
0

LVL 7

Author Comment

ID: 36945658
Okay...sorry I was trying things out.. I see now why what I originally said was wrong. Thanks for correcting!

What @barryhoudini wrote does work better..

Can I filter by multiple criteria though? So say I want to take the average of A, B and C..and then one of where everything else doesn't equal A, B or C (A stored in C2, B stored in C3 and C stored in C4)?

=SUMPRODUCT((\$F\$11:\$F\$51<>\$C\$4 AND \$F11:\$F51<>\$C\$3 AND \$F11:\$F51<>\$C\$2)*\$I\$11:\$M\$51)/MAX(1,SUMPRODUCT((\$F\$11:\$F\$51=\$C\$4)*ISNUMBER(\$I\$11:\$M\$51)))

When I try ^ that formula, I get a #NAME error.

Thanks!
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36945681
Try this version

=SUMPRODUCT(ISNA(MATCH(\$F\$11:\$F\$51,C2:C4,0))*\$I\$11:\$M\$51)/MAX(1,SUMPRODUCT(ISNA(MATCH(\$F\$11:\$F\$51,C2:C4,0))*ISNUMBER(\$I\$11:\$M\$51)))

regards, barry
0

LVL 7

Author Comment

ID: 36945714
ISNA is brilliant! Along with the MATCH combo. I had no clue those functions existed. Thanks so much for the help! It's working great!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilā¦
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
###### Suggested Courses
Course of the Month20 days, 4 hours left to enroll

#### 872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.