+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    08-13-2012
    Location
    Philadelphia, PA, USA
    MS-Off Ver
    Excel 2007
    Posts
    16

    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. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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
    Audere est facere

  3. #3
    Registered User
    Join Date
    08-13-2012
    Location
    Philadelphia, PA, USA
    MS-Off Ver
    Excel 2007
    Posts
    16

    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. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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. #5
    Registered User
    Join Date
    08-13-2012
    Location
    Philadelphia, PA, USA
    MS-Off Ver
    Excel 2007
    Posts
    16

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

    Quote Originally Posted by daddylonglegs View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1