How to SUMIF, with the range and sum range dependent on tables located across multiple sheets

[hutch27]

Background:

• Summary tab - This consists of rows with various general ledger ("GL") account names, each of which has an associated GL Account Number and GL Tab Name in the cell to the right of the preceding item. Additionally, there is a reconciliation column in the cells to the right of each GL Tab Name. Here is a table to visualize this Summary tab:

GL Account Name	GL Account Number	GL Tab Name	Table Name	Balance, per supporting tab
Cash	100	Cash Summary	Table_Cash_Summary
Dividend Income	110	Income	Table_Income
Bank Fees	120	Expenses	Table_Expenses

• GL Tab Names tabs - For each corresponding GL Tab Name, there are unique sheets, each of which has a name equal to the corresponding GL Tab name, and tables, equal to the corresponding Table Name. Tables consist of a variable number of columns, but all will have a GL Account Number Column and an Amount column.

Goal:

1. To create a formula in the Balance, Per Supporting Tab column with the following logic:
   • Using the GL Tab Name and the associated Table Name, sum all values (Amounts column) within the table (which are located on each corresponding GL Tab Name sheet) related to that GL Account Number

Thanks!

 

[igold]

Your SUMIFS formula ("for the Balance per supporting tab") will just about write itself when you start typing. As as example, for your first row "Cash", the formula will look something like this:

=SUMIFS(Table_Cash_Summary[AMOUNT],Table_Cash_Summary[GL Account Number],100,Table_Cash_Summary[GL Account Name],"Cash")

As soon as you type in "=SUMIFS(" and then type the "T" -> all your tables will come up in a little box below the formula (I forget the name of that box at the moment) at any rate, click on the table you want to use. Then as soon as you type an open bracket "[" all the columns of that table will come up in that same box. Likewise click on the column you want, type a comma, and then add your criteria. Continue like this until you have all the Columns and criteria. Remember that each set of column and criteria must be preceded by the Table Name.

Please note that in the sample formula I provided I used "Amount", that Column for you will be the column that you are summing. Also except for the column you are summing which must come first, the order in which you enter the other Columns and Criteria is irrelevant.

You do not need the Tab Name.