+ Reply to Thread
Results 1 to 4 of 4

AverageIF within a subtotal

  1. #1
    Registered User
    Join Date
    01-15-2021
    Location
    North West, England
    MS-Off Ver
    Office Version 18.2008.12711.0
    Posts
    6

    Exclamation AverageIF within a subtotal

    What i want to be able to do is have an AverageIF working within a subtotal.

    From the example in the excel sheet, Im doing countIFS within a subtotal in C2:E13 but i need to be able to expand or change the formula to be able to AVERAGEIF in the neighbouring cells, rather than COUNTIF...

    I want to be able to dynamically populate the table at the top of the Excel sheet when filtering on other columns on the data dump below (row 18 downwards)

    What i need to be able to do in the highlighted yellow area (F2:G13) is average the "Dwell Time" in Column F (or G) IF the cells match the description in column A2:A13.

    The reason being that when i filter on Owner in Column D, it will then tell me, by Outlet Type, what the average customer dwell time is in their Bars, or Restaurants etc.
    Attached Files Attached Files
    Last edited by ChrisNaughton; 01-20-2021 at 09:58 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: AverageIF within a subtotal

    In F2, copied acrss and down:

    =SUMPRODUCT(--($A$19:$A$71=$A2),SUBTOTAL(109,OFFSET(F$19:F$71,ROW(F$19:F$71)-ROW(F$19),0,1,1)))/SUMPRODUCT(--($A$19:$A$71=$A2),SUBTOTAL(103,OFFSET(F$19:F$71,ROW(F$19:F$71)-ROW(F$19),0,1,1)))
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    01-15-2021
    Location
    North West, England
    MS-Off Ver
    Office Version 18.2008.12711.0
    Posts
    6

    Re: AverageIF within a subtotal

    Thanks Glenn, You're the best!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: AverageIF within a subtotal

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. Sorting by Alpha, Adding Subtotal - then removing subtotal - variable data sets
    By gregg_grug in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-03-2019, 07:41 PM
  2. Remove only 1 subtotal layer from Multi Nested Subtotal
    By goawohl in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 10-16-2014, 10:10 AM
  3. [SOLVED] Combine SUBTOTAL and AVERAGEIF?
    By CUFF in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-28-2014, 10:19 AM
  4. Combine SUBTOTAL and AVERAGEIF
    By CUFF in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-27-2014, 08:06 PM
  5. Replies: 1
    Last Post: 09-18-2013, 04:10 PM
  6. Replies: 8
    Last Post: 07-31-2012, 09:41 AM
  7. Subtotal - Delete zero Subtotal and prior rows that calculate to that zero Subtotal
    By Whatsherface in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2012, 08:37 PM

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