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="width: 500"]
<tbody>[TR]
[TD]Table name[/TD]
[TD]Client[/TD]
[TD]Sumif[/TD]
[/TR]
[TR]
[TD]Table1[/TD]
[TD]A[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]Table2[/TD]
[TD]A[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Table3[/TD]
[TD]A[/TD]
[TD]21[/TD]
[/TR]
</tbody>[/TABLE]


Example of Table2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item no[/TD]
[TD]Client[/TD]
[TD]Number of items[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]50[/TD]
[TD]grh[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]100[/TD]
[TD]fjfj[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]200[/TD]
[TD]kyk[/TD]
[/TR]
</tbody>[/TABLE]

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: