Sumif+Vlookup+Match integrated problem !!!

Hi experts,

I have a small problem regarding Sumif+Vlookup+Match function working together.

Lets say I have a pivot table in sheet 1 with some values. On sheet 2 I extract the data from sheet 1 per "area code" and per "row labels" using vlookup and match function together BUT here comes the problem.
In sheet 2, column M, row 27, I get the value of -434865 for the "area code" 1125 but I want the formula to sum all the values for each area code from sheet 1, in this ex its only area code 1125.

The answer should be -375821 by adding row 9 and 26 from sheet 1.

How can I achieve that ?
Any brilliant ideas ?

Mvh
Sonu

I think what you want is:
=sumif(Sheet1!A1:A27,"=1125",Sheet1!L1:L27)
?

Actually not

As you can see on sheet 2, i need to get the data for each "area code" (vertically) and for every row label (horizontally) and I need it to do automatically. The "row label" columns in sheet 1 can change so I don't want to do it manually by choosing the column every time.

How can I integrate "sumif" or some other function within vlookup+match (in sheet 2) ?

oh right, why do you have extra column headers on your sheet2 compared to sheet1? If you match the column headers correctly you could use:
=SUMIF(Sheet1!$A$4:$A$27,"="&Sheet2!$A2,Sheet1!B$4:B$27)
in B2 and copy down and across.

Bcoz the in sheet 1 I can have different pivot tables and on each pivot the column headers can change from 0-999 that's why I cannot use only sumif.

Example.xlsx

See attachment,

That's how I would do it, assuming you don't need the additional column headers that aren't featured on each pivot table.

The only way I can think of doing it as you had it set up would be using an indirect function to return the correct column for your sumif range, the problem with doing this is it would become somewhat "unwieldy" and probably increase calculation time. I think re-arranging the structure of your data to make things easier would be the better way to go here. That said, the following formula in B2 and copies down/across would work for your current set-up.

=SUMIF(Sheet1!$A$3:$A$27,"="&Sheet2!$A2,INDIRECT("Sheet1!"&ADDRESS(3,MATCH(B$1,Sheet1!$A$3:$L$3,0))&":"&ADDRESS(27,MATCH(B$1,Sheet1!$A$3:$L$3,0))))