averageif across worksheets

Hi,

I have several worksheets that are all formatted exactly the same and then one worksheet at the back with various kinds of summary and total data.

An example spreadsheet is attached.

Each worksheet, except the summary one, represents the sales and costs of an individual store from January through July 2013.

In the Summary worksheet I'm trying to get the average sales and costs for the stores.

I'm using the "averageif" function because one of the stores is closed down during the year and has $0 sales. I'd like to ignore these.

I can't get the "averageif" to work -- I keep getting "value". Please see specifically the yellow shaded cells in the "Summary" tab attached.

Thanks.

Hi Biff -- those unfortunately aren't the real sheet names. There are also about 15 more sheets. I think I could adapt what you write, however.

Thanks but a lot of my data will just have the 0s in it. I would love to use the average formula but it just won't work

Thanks - but what are the Index and Frequency functions doing?

Awesome! This is great. Thank you very much. Sorry for having to ask but I tried to google the Frequency and Index functions and I still wasn't getting it. I can't put formulas in my work that I don't understand. Thanks again.

Originally Posted by Tony Valko

We know that an average is a sum divided by a count.

So, we get the sum:

SUM(Store1:Store3!C6)

And then we use the FREQUENCY function(s) to get the count of non-zero numbers.

The FREQUENCY function performs a series of "count if" operations based on the arguments provided.

The first FREQUENCY function:

FREQUENCY(Store1:Store3!C6,-0.000001)

Performs these "count ifs":

Count if Store1:Store3!C6 is <= -0.000001
Count if Store1:Store3!C6 is > -0.000001

The second FREQUENCY function:

FREQUENCY(Store1:Store3!C6,0)

Performs these "count ifs":

Count if Store1:Store3!C6 is <= 0
Count if Store1:Store3!C6 is > 0

In order to get the correct average we need to count both the negative numbers and the numbers that are >0 in the data.

We do that by adding together:

Count if Store1:Store3!C6 is <= -0.000001
Count if Store1:Store3!C6 is > 0

Since the second FREQUENCY function performs 2 "count ifs", and the one we need is the second one, we use the INDEX function to return the second "count if":

INDEX(FREQUENCY(Store1:Store3!C6,0),2) = Count if Store1:Store3!C6 is > 0

So, to get the average across the sheets and ignore the 0 values:

=SUM(Store1:Store3!C6)/(FREQUENCY(Store1:Store3!C6,-0.000001)+INDEX(FREQUENCY(Store1:Store3!C6,0),2))

You might think: WTH, why do we need to use such a convoluted formula to do this?

Well, the answer is because MS didn't make this easy for us. They didn't give the AVERAGEIF function the ability to calculate across multiple sheets without using extreme "trickeration". So, we come up with these convoluted (but "slick") alternatives.

done -- marked as solved. I tried to "add reputation" to Tony but it said I had to "spread the reputation around"...or something like that. I guess he had helped me previously and I added then, but not enough to others (although i don't use the forum frequently).

Originally Posted by Tony Valko

Try this...

On the Summary sheet enter this formula in C6:

=SUM(Store1:Store3!C6)/(FREQUENCY(Store1:Store3!C6,-0.000001)+INDEX(FREQUENCY(Store1:Store3!C6,0),2))

Copy down to C7 then across to I6:I7.

Tony - This solution worked great for me. I only needed to average the contents of a single cell across multiple sheets and this was by far the simplest solution compared to all the VBA scripts being suggested on other forums I checked. Thanks