sumif over dynamically changing tab names

[Bering]

Hello,

I have a spreadsheet with a number of tabs (say 3), each contacting a table starting from cell A1, except for a tab called "Summary".

The structure of the table is the same in each tab (4 columns - please see example below) and each table has the same name as the corresponding tab (worksheet).

What I would like to do is:

in tab "Summary"

create a sumif formula that refers to the Table Name in column A and sums all the items relative to Client A:
I have been trying for quite some time to amend the below formula so that it is dynamically linked to cells A2-A4 but I am stuck..

Formula in tab "Summary", cells C2 - C4

=SUMIF(Table2[[#All],[Client]],Summary!C2,Table2[[#All],[Number of items]])


Tab Summary, Range A1 - C4

Table name	Client	Sumif
Table1	A	600
Table2	A	50
Table3	A	21

<tbody>
</tbody>

Example of Table2

Item no	Client	Number of items	Description
1	A	50	grh
2	B	100	fjfj
3	C	200	kyk

<tbody>
</tbody>

I the above is not clear, please do let me know and will try my best to clarify my query.

Any suggestions?

Many thanks.

 

[James006]

Hello,

If I understand correctly ... the dynamic part is Table 2 ... which you would like to have as a variable ...

Are you familiar with the Indirect() function ...?

 

[Bering]

Hello, well I tried this but of course it does not work... thanks

SUMIF(INDIRECT(A2)[[#All],[Client]],Summary!C2,INDIRECT(A2)[[#All],[Number of items]])

 

[Bering]

I think I got it

SUMIF(INDIRECT($A2&"[[#All],[Client]]"),Summary!C2,INDIRECT($A2&"[[#All],[Number of items]]"))

Thank James006 for the tip.

 

[James006]

Well done ...!!!

Congratulations ... :wink: