=AVERAGEIF(A2:A11,"A",B2:D

Solved

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!

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)/SU

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!

11 Comments

=AVERAGEIF(A2:A11,"A",B2:D

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

=IFERROR(AVERAGE(IF($F$11:

confirmed with CTRL+SHIFT+ENTER

or a non-array version with SUMPRODUCT

=SUMPRODUCT(($F$11:$F$51=$

regards, barry

@StephenJR: =AVERAGEIF(A2:A11,"A",B2:D

regards, barry

27389620.xls

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

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<>

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

Thanks!

Title | # Comments | Views | Activity |
---|---|---|---|

copying from excel to word | 2 | 31 | |

Excel remove Chars in Conditional formating. | 6 | 19 | |

How to full copy entire table from one Excel file to another? | 6 | 21 | |

Status Change | 3 | 13 |

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

Connect with top rated Experts

**15** Experts available now in Live!