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):
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?