Averageif from columns of different tables?

Hi all,

I've got tables in different sheets in a relatively large workbook that I need to grab the total average across columns from the different tables.

The best I could come up with is something like:

=SUMPRODUCT(SUMIF(INDIRECT({"table1","Table3"}&"[propertytype]"),"detached",INDIRECT({"table1","Table3"}&"[price]")))/SUMPRODUCT(COUNTIF(INDIRECT({"table1","table3"}&"[propertytype]"),"detached"))

Is there a better way to do this?

See attached for an example of what I'm talking about.

Hi,

If the tables have the same structure - you can use this in Sheet1, B2 and across:
AVERAGE(Sheet2:Sheet3!B2)

I can't think of a much more concise way. I think SUM rather than SUMPRODUCT does the same thing.

You can probably do something complex to end up with a single-column array built from all of the tables, and then wrap that in AVERAGE, but I doubt it would be better than just using SUMIF and COUNTIF as you have done.

I need to use criteria from the first column though? hence why I am using sumif and countif

I think you have to get all your tables to be in same structure in order to be able to have a summary sheet.
you can make a PIVOTABLE in each sheet showing the SUM and count of each item.
(Let's say that all of them will start at line 7, like in the attached example)

then in your summary table do a sum across sheets (SUM(Sheet2:Sheet3!B7)), and you will be able to make the AVERAGE of each item by dividing the SUM by COUNT.

See attached, I have applied it in your sample workbook.