Reference and sum different sheet

Create a named range, for example, Sheetnames, and enter the following formula into the Refers to field:

Formula:

Please Login or Register  to view this content.

Note, to use this method you will need to save the file as a macro enabled workbook (.xlsm).
Then, in a helper column in Owners & Total Acreage tab, enter this formula to list the tab names in your workbook (I pasted it on G2):

Formula:

Please Login or Register  to view this content.

Copy down as far as required. I copied it down to row 200.
Then create another named range, for example Owners, and enter the following formula into the Refers to field:
=OFFSET('Owners & Total Acreage'!$G$2,,,COUNTA('Owners & Total Acreage'!$G$2:$G$200)-COUNTIF('Owners & Total Acreage'!$G$2:$G$200,""),1)

Then for your SUMIF function, use this formula:

Formula:

Please Login or Register  to view this content.

Ranges A2:A1000 and B2:B1000 to be adjusted to your needs.

I’m not sure the file attached will work, because of translation issues that may occur in the hidden function GET.WORKBOOK, but I send it anyway. I made a copy of the first tab just to have a second tab to get data from.
Note that my results are double of yours.
I hope this will help you accomplish what you need.

Good luck!

Hi, there

No doubt that piovottable is born to target such queries

I provide a solution with formulae.

1. Define a table in the tab Owner & Acreage with a name PROP

2. In columnA of tab Owners & Total Acreage, an array formula sift out duplicates of owners in the dataset;

3. then sum up all acreages under the owners respectively with SUMIF func,

Formula:

Please Login or Register  to view this content.

Cheers!