Formula required for sum!!

[jsk_lge]

Hi there,
i have two sheets in which one of the sheet contains raw data branch & model wise (the sheet name is "data is like this" in attached file).
in raw data, branch is in column and model is in row.
i need a formula to arrange this data in which branch & models both are in column.

pls suggest a formula to do so.

pls refer to attached file.


Thanks in advance.

 

[Nebu]

Hi:

Is this what you are looking for.

Thanks

 

[Hui]

C2: =SUMPRODUCT(('Data is like this'!$A$2:$A$21=$A2)*('Data is like this'!$B$1:$U$1=$B2)*('Data is like this'!$B$2:$U$21))
copy down
or see attached file:

To understand this Refer to: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[Hui]

JSK

You should examine both these solutions

Nebu's solution uses a Sum/If combination in an array formula

I use a strategy of multiplying arrays and do it in a Sumproduct function and hence is normally entered

 

[jayalaxmi]

Hi:

Is this what you are looking for.

Thanks

interesting one...took sum time to understand but yes its amazing....

 

[jayalaxmi]

Hi:

Is this what you are looking for.

Thanks

Can't we use vlookup for this and then sum it out as an alternative???is there any option of doing it

 

[Nebu]

@jayalaxmi

No, vlookup can give only one dimensional values , where as the OP want to match both rows and column and get the total. Hence, vlookup is not an option.

Thanks

 

[jayalaxmi]

okies and thankyou @Nebu