Sum according to unique data in row and column

[vijayca]

Dear Experts,

I have different teams in rows which reflect more than one in randomly and different places in different columns with values in matrix in data sample sheet. I need sum of values for teams according to places in result working sheet. For better understanding I have attached template and have shown result for Team A which is marked in yellow colour. Since huge data wont possible to do addition manually. Need simple formula base result. Pls. help.

Thanks in advance

[Squeaky]

Here is my solution. I sorted the data sample according to Place, then used a sumif formula to get the totals for all combinations. I then picked the 3 you were looking for. Hide what you don't want to see.
Good luck!

[Fluff13]

Another option, in D4 copied down
=SUMPRODUCT(('Data sample'!$B$4:$B$11=C4)*('Data sample'!$C$2:$I$2=B4),'Data sample'!$C$4:$I$11)

[Squeaky]

That's great. I need to study SUMPRODUCT.

[vijayca]

Above formula works fine. But unfortunately neither I can rearrange rows or align or reshuffle columns to suite the formula due voluminous data and same is to share to other teams in part for processing and then consolidation again. Is it possible to work only on table range from B2 to J11 in "Data sample" worksheet without further manual activities like done from L2 to AI12 in "Data sample" worksheet and get result ? Appreciate your efforts.

Regards,

[Squeaky]

Not sure I understand. Did Fluff13's formula not work? It should work by itself without making additions. If your chart is bigger then adjust the points inside the formula and it should work. Rearrangements are not needed as far as I can tell.

[vijayca]

Fluff13's formula worked only after he additionally rearranged the data from L2 to AI12 in "Data sample" worksheet. But if you refer my original attachment I had given table from B2 to J11 in "Data sample" worksheet. By referring that table only I need formula based result in "Result working" worksheet. Hope I cleared your doubts.

Regards,

[Fluff13]

I did not rearrange your data in any way, the formula looks at the data in cells C4:I11 based on the values in B4:B11 & C2:I2
In other words, it does exactly what you asked for.
Why not try it?

[Squeaky]

Hi vijayca,
It was my solution, not Fluff13, where I added the extra columns. I did not rearrange the originals either. Fluff13 provided a solution that works with only the provided information by putting his formulas directly in the appropriate cells, D4-D6 of the Result tab. I plugged it in and verified that. Go look at his first entry in this chain. It works!

Squeaky.