Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .