# AverageIF within a subtotal

1. ## 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.

2. ## 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)))

3. ## Re: AverageIF within a subtotal

Thanks Glenn, You're the best!

4. ## 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.

#### Thread Information

##### Users Browsing this Thread

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

#### 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