Thread: How to build index formula?

Hi People

Thanks in advance for your time and effort......

I have a workbook in which i am in leymans terms "transposing & summing" data on the same worksheet from row / column to column / row.
I have been doing the labourious job of summing data in the worksheet and trying to auto fill, however this does'nt work.
I think i may need to use an INDEX function but have no idea how to construct it, any help would be greatly appreciated.

I have attached a sample workbook for your info, i realise my explanation is rubbish, hope the sample will be self explanatory.

Ta

Si

In C32 enter:

=SUMPRODUCT(($I$2:$BD$2=$B32)*($C$3:$C$21=C$23),$I$3:$BD$21)

copy down and across the table.

If the Row Titles are as implied unique a SUMIF would suffice (no need for SUMPRODUCT per se)

=SUMIF($I$2:$BD$2,$B32,INDEX($I$3:$BD$21,COLUMNS($C32:C32),0))
applied to matrix