Combining Subtotal with criteria

I have hidden rows, and the column that has these 'unhidden' results need to be averaged, but only if it is from a corresponding criteria met. In my case, I want to average the "response time" of certain users, and response times are randomly sampled, and I hide the rows for data that did not get a sample response time. So I want to average each specific users response times.

I have tried combining VLOOKUP by using:

Please Login or Register  to view this content.

Which won't even be accepted. I also trued Index and Match:

Please Login or Register  to view this content.

And that has #N/A return.

Are there any other tricks to utilize the ability to average hidden cells that also must fit a criteria?

I have attached a clean spreadsheet with no P.I.I. on it with the layout of the problem recreated. Excel '13 in use. Thanks a bunch!

=IFERROR(AVERAGEIF($F$4:$F$530,F$536,$V$4:$V$530),"")

The downside is that The average for cells V4:V530 takes the hidden cells as well. That's why I need subtotal's 101 function. Try it for yourself, do an =AVERAGE on cells V4:V41 (the end of the samples for User1), and you get 6:13, meanwhile, if you actually take the values of that set (V4:V41), and ignore the 0:00 response time, the average is 7:52. The cells in column V that display a 0:00 response time are ones that are not sampled, so I would like to not include those 0's in the data thats being averaged.

avk has not allowed for the hidden rows.

You don't need to use subtotal, as you're only hiding blanks in column U, you can use averagifs to ignore the blanks instead, this will do it.

=AVERAGEIFS(V4:V530,F4:F530,F536,U4:U530,"<>")

You will need to correct your headings though, in F536, you have 'User1, Sample' but in F4:F50 you have 'Sample, User1' The formula will not be able to match them together.

You can do i with SUBTOTAL, but with your example, there is no need. This is messy and volatile.

=AVERAGE(IFERROR(SUBTOTAL(1,OFFSET($V$1,IF($F$4:$F$530=F536,ROW($F$4:$F$530)-1),0)),""))

Array confirmed with Shift Ctrl Enter.

Haha, I love that with excel, once the working answer is found, and you study it, its like "ohhhh, duh, that makes sense". Thanks Jason, those numbers check out with the correct averages. That is the first time that I've seen OFFSET used with IF. I'll have to keep that in mind. Cheers!

Originally Posted by PetGiraffe

That is the first time that I've seen OFFSET used with IF.

You can use anything with anything as long as the correct data type is being returned.
A lot of people are afraid to try a method that they haven't seen used before. What's the worst that could happen?
Thinking out of the box always helps, often there are several ways to get the correct result and the method you first think of might not always be the best one. In the case of your requirement, I would choose AVERAGEIFS for efficiency over SUBTOTAL and OFFSET. It might not be practical if the criteria ar more complex, or change frequently, but here it is simple enough to use.