How-to AverageIF Excluding Zeros Across Excel Worksheet Tabs

I didn’t receive any sample formulas from Friday’s post that included a Friday Challenge, so maybe I stumped everyone when asking you to create an AverageIF Formula Excluding Zeros Across Worksheet Tabs.

Here is the post so that you can learn more about the multi-worksheet tab range reference that was demonstrated.

Sum Across Excel Worksheets Tabs
Sum Across Excel Worksheet Tabs

How-to Easily Sum Across Excel Worksheet Tabs (Plus Friday Challenge)

The Question

In that post I asked if you could create a formula that would calculate the Average excluding zeros (0) across several Excel worksheet tabs using the simple range reference shown in that posting and video.

Maybe everyone thought that AverageIF would work and why bother finding a formula that will work.  However… using the simple multi-sheet / multi-tab reference doesn’t work with AverageIF.

AverageIF Across Excel Worksheet Tabs AverageIF Function Result
AverageIF Across Excel Worksheet Tabs AverageIF Function Result

The Solution

We need to go back tot he basics like you may have had to use in Excel 2003, before there was an AverageIF statement.

One such solution is to Sum all values across all sheets and divide it by the count of non zero or non-blank values.

We will use a simple sum function for the numerator.

Then we will use a combination of Index and Frequency to count the number of non-blank and non-zero values across all sheets.

The AverageIF Formula Excluding Zeros Across Worksheet Tabs

Assuming your data is in Cell A2 across sheets grouped between “Dec” and “Jan” as you see here:

Multiple Excel Tabs December to January
Multiple Excel Tabs December to January

Here is the entire formula:

=Sum(Dec:Jan!A2)/INDEX(FREQUENCY(Dec:Jan!A2,0),2)

You can use the Formulas>Evaluate Formula action to check out the results step by step:

AverageIF Across Excel Worksheet Tabs Formula
AverageIF Across Excel Worksheet Tabs Formula

The numerator of the formula would as follows:

=Sum(Dec:Jan!A2)/

  • This is pretty straight forward. If you don’t understand the syntax here, please visit the URL above in the article to learn more.

    AverageIF Across Excel Worksheet Tabs Formula
    AverageIF Across Excel Worksheet Tabs Sum Component
  • AverageIF Across Excel Worksheet Tabs Sum Component Result
    AverageIF Across Excel Worksheet Tabs Sum Component Result

The denominator of he formula would be as follows:

INDEX(FREQUENCY(Dec:Jan!A2,0),2)

  • Let’s break this formula down.  Try and start from the inner most parts and move out when evaluating formulas:
    • Frequency, this part of the formula will group all the values in cell A2 across the worksheets into those that have a value of zero and those that don’t.

      AverageIF Across Excel Worksheet Tabs Sum Component Result
      AverageIF Across Excel Worksheet Tabs Frequency Component
    • AverageIF Across Excel Worksheet Tabs Frequency Component Result
      AverageIF Across Excel Worksheet Tabs Frequency Component Result
    • Index, this will get the 2nd value of the results from the Frequency function.

      AverageIF Across Excel Worksheet Tabs Frequency Component Result
      AverageIF Across Excel Worksheet Tabs Frequency Component Result
    • And your final result will be the Summation divided by the Index/Frequency Calculation
    • AverageIF Across Excel Worksheet Tabs Result
      AverageIF Across Excel Worksheet Tabs Result

For a better understanding of how this formula works, please watch the video below.

Video Demonstration

Do you have an alternate solution/formula?  Let me know in the comments below if you have a better solution.  Thanks!

Steve=True