# Excel 2010 AVERAGEIF function to average two different columns on two different tabs

1. ## Excel 2010 AVERAGEIF function to average two different columns on two different tabs

Hi All!

I currently have the following Formula:

=IFERROR(AVERAGEIF('Property Openings'!\$E:\$E,A12,'Property Openings'!\$H:\$H),0)

Now it works great but we have decided to include a second column in order to determine the average. This column is on the 'Property Filled' tab and the table is exactly the same, column E is still the 'IF' based on cell A12 and column H includes the numbers to be averaged. I tried the following formula but it did not work:

=IFERROR(AVERAGEIF('Property Openings'!\$E:\$E,A12,('Property Openings'!\$H:\$H,'Property Filled'!H:H)),0)

How can I make this work? Thank you for any assistance provided.

2. ## Re: Excel 2010 AVERAGEIF function to average two different columns on two different tabs

Are you saying that you want to average two different columns based on one criteria column? You won't be able to use AVERAGEIF for that - try this approach

=(SUMIF('Property Openings'!\$E:\$E,A12,'Property Openings'!\$H:\$H)+SUMIF('Property Openings'!\$E:\$E,A12,'Property Filled'!\$H:\$H))/COUNTIF('Property Openings'!\$E:\$E,A12)/2

3. ## Re: Excel 2010 AVERAGEIF function to average two different columns on two different tabs

Sorry, just realized something I was missing thanks to your post. I want to average two different columns based on the criteria column on both tabs, not just based on the Property Openings tab.

Theoretically, I want to average the two averages below.

=IFERROR(AVERAGEIF('Property Openings'!\$E:\$E,A12,'Property Openings'!\$H:\$H),0)
=IFERROR(AVERAGEIF('Property Filled'!\$E:\$E,A12,'Property Filled'!\$H:\$H),0)

4. ## Re: Excel 2010 AVERAGEIF function to average two different columns on two different tabs

OK, but a similar approach is still required, i.e.

=IFERROR((SUMIF('Property Openings'!\$E:\$E,A12,'Property Openings'!\$H:\$H)+SUMIF('Property Filled'!\$E:\$E,A12,'Property Filled'!\$H:\$H))/(COUNTIF('Property Openings'!\$E:\$E,A12)+COUNTIF('Property Filled'!\$E:\$E,A12)),0)

5. ## Re: Excel 2010 AVERAGEIF function to average two different columns on two different tabs

OK, but a similar approach is still required, i.e.

=IFERROR((SUMIF('Property Openings'!\$E:\$E,A12,'Property Openings'!\$H:\$H)+SUMIF('Property Filled'!\$E:\$E,A12,'Property Filled'!\$H:\$H))/(COUNTIF('Property Openings'!\$E:\$E,A12)+COUNTIF('Property Filled'!\$E:\$E,A12)),0)

Worked perfectly! I truly appreciate the assistance.

There are currently 1 users browsing this thread. (0 members and 1 guests)