Averaging values of a column (with possible empty/blank cells) corresponded to row number of satisfied condition in another column

[MahTah]

Hi there,
To declare my problem I'd use a simple example here. On the table below, I want to have the average of values in column "B" which are corresponded to the cells on column "A" with a value equal to cell "C2" or "101". As it can be seen in the table, some corresponded cells are empty (blank). If I use either of the formulas below, I would end up with a wrong value, since the blank cells in these formulas will interpreted as "0"!

=SUMIF($A$2:$A$15,C2,$B$2:$B$15)/COUNTIF($A$2:$A$15,C2)

or

=AVERAGE(IF($A$2:$A$15=C2,$B$2:$B$15)) (with CTRL+SHIFT+ENTER)

Any idea for a solution (Appreciate your help in advance)!?

A B C
100 32.5 101
100 33.4
100 34.5
100 31.6
100 34.9
101 38.4
101
101
101 39.1
101 31.1
102 32.4
102 34.1
102 33.3
102 34.9

 

[MARZIOTULLIO]

	a​	b​	c​	d​	e​
1​	100​	32.5​	101​		36.2​
2​	100​	33.4​
3​	100​	34.5​
4​	100​	31.6​
5​	100​	34.9​
6​	101​	38.4​
7​	101​
8​	101​
9​	101​	39.1​
10​	101​	31.1​
11​	102​	32.4​
12​	102​	34.1​
13​	102​	33.3​
14​	102​	34.9​

<tbody>
</tbody>

E1=AVERAGE(IF(A1:A14=C1,IF(B1:B14<>"",B1:B14))) Control+Shift+Enter

 

[Ron Coderre]

Using your posted example,
Here are two options:

If you are using Excel 2007 or later:

=AVERAGEIF($A$2:$A$15,C2,$B$2:$B$15)

Otherwise, try this regular formula:

=SUMIF($A$2:$A$15,C2,$B$2:$B$15)/SUMPRODUCT(($A$2:$A$15=C2)*($B$2:$B$15<>""))

Is that something you can work with?

 

[RCBricker]

Hi there,
To declare my problem I'd use a simple example here. On the table below, I want to have the average of values in column "B" which are corresponded to the cells on column "A" with a value equal to cell "C2" or "101". As it can be seen in the table, some corresponded cells are empty (blank). If I use either of the formulas below, I would end up with a wrong value, since the blank cells in these formulas will interpreted as "0"!

=SUMIF($A$2:$A$15,C2,$B$2:$B$15)/COUNTIF($A$2:$A$15,C2)

or

=AVERAGE(IF($A$2:$A$15=C2,$B$2:$B$15)) (with CTRL+SHIFT+ENTER)

Any idea for a solution (Appreciate your help in advance)!?

A B C
100 32.5 101
100 33.4
100 34.5
100 31.6
100 34.9
101 38.4
101
101
101 39.1
101 31.1
102 32.4
102 34.1
102 33.3
102 34.9

try this

change cell references and ranges to match your needs

=AVERAGEIF(A93:A106,C93,B93:B106)

 

[MahTah]

Thanks alot MARZIOTULLIO! You saved me from frustration!

 

[MARZIOTULLIO]

Hi Mah Tah

You are welcome

you can use also

E1=AVAREGEIFS(B1:B14,B1:B14,"<>",A1:A14,C1)

 

[MahTah]

Thank you all for your time! I can just guess that the Ron Coderre first solution might be faster in a big set of data!