+ Reply to Thread
Results 1 to 2 of 2

AVERAGEIFS - Multiple Columns, Multiple Criteria

  1. #1
    Registered User
    Join Date
    05-14-2019
    Location
    USA
    MS-Off Ver
    MS Office 2013
    Posts
    1

    AVERAGEIFS - Multiple Columns, Multiple Criteria

    Would someone please be able to help with a formula to average multiple columns based on multiple criteria?

    Attached is example data. I need to average tab 1 column B, if tab 1 column A=6/30/2019, combined with averaging tab 2 column B, if tab 2 column A=6/30/2019 in the same formula.

    In the attached example data, the correct answer should be 29%.

    I have tried the formula =AVERAGE(AVERAGEIF('Tab 1'!A:A,"6/30/2019",'Tab 1'!B:B),AVERAGEIF('Tab 2'!A:A,"6/30/2019",'Tab 2'!B:B)) but it comes up with the wrong result.

    I think I need to use AVERAGEIFS but not sure how to include multiple columns in the average range.
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: AVERAGEIFS - Multiple Columns, Multiple Criteria

    Taking the average of averages will only give you an accurate result if the number of items in each average is the same (or if both averages are 0).

    Try this:

    =(SUMIF(A:A,DATE(2019,6,30),B:B)+SUMIF('Tab 2'!A:A,DATE(2019,6,30),'Tab 2'!B:B))/(COUNTIF(A:A,DATE(2019,6,30))+COUNTIF('Tab 2'!A:A,DATE(2019,6,30)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. averageifs with multiple criteria
    By cchi3k in forum Excel General
    Replies: 2
    Last Post: 05-12-2017, 06:14 AM
  2. [SOLVED] AVERAGEIFS with multiple criteria
    By bibu in forum Excel General
    Replies: 5
    Last Post: 03-22-2014, 03:28 PM
  3. AVERAGEIFS(), with multiple criteria
    By Jkember in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2014, 06:33 PM
  4. AVERAGEIFS Multiple Criteria
    By qhoney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2014, 03:58 PM
  5. [SOLVED] Averageifs with multiple criteria
    By jbillyo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2013, 04:13 PM
  6. [SOLVED] AVERAGEIFS using multiple ranges and multiple criteria
    By LindsayS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2013, 01:49 PM
  7. AverageIfs for multiple criteria in different columns
    By HBEE in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-05-2013, 12:21 PM

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