AVERAGEIF - single criterion in multiple columns?

Hi everyone,

I'm trying to calculate the average attendance for a team, using the AVERAGEIF function.

In this example, I want to calculate the average attendance of Tottenham, which makes "Tottenham" the criteria. The problem is that Tottenham could either be the home team or the away team, and thus be in either column A or B. When using AVERAGEIF, Excel only seems to include rows if "Tottenham" in the first code column.

This is the code I use:

=AVERAGEIF(A2:B9,"Tottenham",C2:C9)

I've included a screenshot, but I use a Norwegian version of Excel, so it's probably bit confusing.

I'll be very grateful for your help!

Averageif.JPG

Try

=SUMPRODUCT(((A1:A50="Tottenham")+(B1:B50="Tottenham"))*(C1:C50))/SUMPRODUCT((A1:A50="Tottenham")+(B1:B50="Tottenham"))

Thanks John! I may be doing something wrong, but I keep getting the #VALUE! error when trying your suggestion.

Check the values in the range (which you have used).

OR

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by Ash_Maverick

=(averageif(a2:a9,a13,c2:c9)+averageif(b2:b9,a13,c2:c9))/(countif(a2:a9,a13)+countif(b2:b9,a13))

Thanks Ash, I appreciate it!

However, I'm afraid that gives an incorrect result, as that would sum the average from the A and B column, and then divide that sum again. It works for the example I gave, but if you change for example A9 from "Blabla" to "Tottenham", your formula reports the average to be 33 674, while 56 882 is correct...

Well spotted: I just posted my test without referring to the Op's post.

Originally Posted by Jonmo1

John's formula from post #2 returns #Value because there are Headers (Text Strings) in row 1
=SUMPRODUCT(((A1:A50="Tottenham")+(B1:B50="Tottenham"))*(C1:C50))/SUMPRODUCT((A1:A50="Tottenham")+(B1:B50="Tottenham"))

That should be

=SUMPRODUCT(((A2:A9="Tottenham")+(B2:B9="Tottenham"))*(C2:C9))/SUMPRODUCT((A2:A9="Tottenham")+(B2:B9="Tottenham"))

Of course, thanks a lot, both of you!

You're welcome.