sumifs multiple worksheets with 2 variables

[melvin]

Hi all,

I have been trying different formulas from the forum for similar queries, but couldn't get it to work.

I am trying to consolidate data from multiple worksheets, using 2 variables as intersection

i.e. Store id & Sales. tried sumif, indirect, but couldn't get it to work.

Appreciate any help

Melv.

 

[Hui]

The Store ID isn't in a field on any tabs or in any Tab Names ?

where to find that ?

 

[melvin]

Woahhh..What was I thinking. File updated Hui. Thanks mate

 

[Hui]

N7: =SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&TabList&"'!B5:B14"),,MATCH(N$6,$N$6:$FP$6,0)-1),INDIRECT("'"&TabList&"'!A5:A14"),$D7))

Copy across and down

See attached file:

 

[melvin]

Champion mate.
What does this part of the formula do
MATCH(N$6,$N$6:$FP$6,0)-1), How does this gets information from all the sheets

INDIRECT("'"&TabList&"'!A5:A14") - Not sure what this does. Appreciate your guidance. No rush mate.

Have a great weekend.

Melv.