Averageif Always Returns the Wrong Answer

[Coachcasa]

Hi. I'm trying to use an averageif formula and I think I have it correct, but it keeps returning the same value that is wrong. Here is the formula I'm using:

=AVERAGEif($E$2:$E$1000,"George H.W. Bush",$F$2:$J$1000)

Here is the table I'm pulling from:

1	E	F	G	H	I	J
2	George H.W. Bush	3	4	2	4	3
3	George H.W. Bush	1	2	3	4	5
4	George W. Bush	4	4	3	5	3

It keeps giving me an answer of 2, but I know the proper answer should be 3.1. Even if it averaged all three rows it should still be 3.33. I'm just using this as a test table, so I don't have any info below row 4 right now, but it will populate down. My formula is located in cell K2. I tried putting a list of presidents in K3-K23 and using the cell reference instead of the name in quotes, but that just gave me an error. Can anyone help me here and explain what I'm doing wrong? Thanks.

 

[RoryA]

For functions like AVERAGEIF (and COUNTIFS, SUMIF etc) all the ranges need to be the same size and shape, which is why yours is not working. You could do something like:

=SUMPRODUCT(($E$2:$E$1000="George H.W. Bush")*($F$2:$J$1000))/(COUNTIF($E$2:$E$1000,"George H.W. Bush")*5)

 

[6StringJazzer]

The cells that are evaluated for the average are mapped to the same shape as the range for the matching criteria. So in your case, the average is computed using $F$2:$F$1000.

 

[6StringJazzer]

For functions like AVERAGEIF (and COUNTIF, SUMIF etc) all the ranges need to be the same size and shape

Just a technicality: For AVERAGEIF (and SUMIF) the ranges do not have to be the same size and shape, that is, you will not get an error if they are not. But if they are not, it will use the size and shape of the first argument to resize the third argument for calculating the average, and this is usually not what the user intends. (COUNTIF only specifies one range.)

 

[Coachcasa]

For functions like AVERAGEIF (and COUNTIF, SUMIF etc) all the ranges need to be the same size and shape, which is why yours is not working. You could do something like:

=SUMPRODUCT(($E$2:$E$1000="George H.W. Bush")*($F$2:$J$1000))/(COUNTIF($E$2:$E$1000,"George H.W. Bush")*5)

When you say the same size and shape are you meaning that every cell has to have the same

 

[Coachcasa]

Thanks, everyone for your answers and thank you Rory for giving me a formula that works! I never realized this, so I've learned something new today.

 

[RoryA]

The implication was that they need to be the same size and shape for the function to work properly.

 

[RoryA]

When you say the same size and shape are you meaning that every cell has to have the same

No, just that if the first range is 1 column by 100 rows, the other ranges should also be 1 column by 100 rows. As Jeff pointed out, the function will silently resize your ranges to suit that restriction - so for example if you typed:

=SUMIF(A1:A100,"test",B1)

that would actually be evaluated as:

=SUMIF(A1:A100,"test",B1:B100)