I found another thread with the solution to at least allow me to run an AVERAGEIF like function across multiple worksheets, but the formula is pretty long and it's not something I'll remember...plus the time to type out such a long formula won't be efficient for me since I use AVERAGEIF all the time. It's basically this formula:
=SUM(comp1:comp4!B53)/(FREQUENCY(comp1:comp4!B53,-0.000001)+INDEX(FREQUENCY(comp1:comp4!B53,0),2))
Here's what I'm trying to do.
I have four worksheets in a single spreadsheet file named comp1, comp2, comp3, and comp4
There's a specific cell (B53) in each worksheet with data I want to average if it's greater than 0. Are the days where I could just go through each worksheet holding ctrl and selecting the cells I want to AVERAGEIF >0 officially defunct in excel 2016. If so, that's irritating and I have no idea why excel would dump such a useful function.
Is there an alternative solution that's as easy to use as the traditional AVERAGEIF function across multiple worksheets, or at least something that is condensed to save time on writing the formula? I know if I have to keep apply the above formula, I'll have to come back to this thread 20x before it sinks in. Anyways...just hope there's an easier way to perform this function.
I'd even be willing to settle for a snippet of VBA code or even a plugin if one's available?
Thanks for any tips!!!!
Bookmarks