Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to write some data to an Excel file, and I want the first page to show a summary of the data that are spread all over the sheets. The individual data items on a sheet can be included or excluded, and I want the summary to be calculated only on the included values.

Thus, I thought I'd use a formula like this to show, for example, an average (for which empty fields shouldn't be counted, either):

=AVERAGE(IF('dataSheet1'!$B:$B=1,IF('dataSheet1'!$I:$I<>"",'dataSheet1'!$I:$I,""),""))

If I have this formula in dataSheet1, everything works fine. If I have this formula in the summary sheet, I need to enter it as an array formula, and I have to specify the exact ranges to make it work. Is there a way (aside from adding the average in dataSheet, and then referencing the result in summary) to make the formula work without having it turn into an array formula?

share|improve this question

1 Answer 1

Average already does excludes empty values. Thats why you can take the average of I:I and not get almost 0 (because its mostly empties), but to include your B:B check you can do something like this:

=SUMIF(dataSheet1!B:B,1,dataSheet1!I:I)/COUNTIF(dataSheet1!B:B,1)
share|improve this answer
    
thanks for the heads-up about AVERAGE. I had been using SUMIF and COUNTIF before, but these do include empties in my hands, so your solution won't work for me. –  Jonas Jul 1 '10 at 18:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.