SUMPRODUCT - Matrix (Adding Multiple Columns - Criteria Top and Side)

[MixedUpExcel]

Hi,

Apologies if this has been covered.. I can find various posts on Sumproduct but can't quite get my head around what I need to do to get this to work.

Please can someone help me with the final part of the formula?

My table is below. This is a mock up that I can then expand across my business data.

I will have it for 12 months (2 columns per month) but I only want to add up the values under 'Sales Value' columns.

If I am just looking for a single item down the left (in this example - 1 persons name) I can get this to work. Cell F32 (with the formula showing in Cell G32)

However, I need to get the Sales Value over all of the months for the Groups - which will include (in most cases) more than 1 name per group. Cells C32 to C35

The names are determined by the groups they are in - Cells B38 to C46.

Hopefully, this table helps show what I am looking for.

[TABLE="width: 883"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]JAN[/TD]
[TD][/TD]
[TD]FEB[/TD]
[TD][/TD]
[TD]MAR[/TD]
[TD][/TD]
[TD]APE[/TD]
[TD][/TD]
[TD]MAY[/TD]
[TD][/TD]
[TD]JUN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]SALES VALUE[/TD]
[TD]QTY[/TD]
[TD]SALES VALUE[/TD]
[TD]QTY[/TD]
[TD]SALES VALUE[/TD]
[TD]QTY[/TD]
[TD]SALES VALUE[/TD]
[TD]QTY[/TD]
[TD]SALES VALUE[/TD]
[TD]QTY[/TD]
[TD]SALES VALUE[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SIMON[/TD]
[TD]208[/TD]
[TD]127[/TD]
[TD]502[/TD]
[TD]84[/TD]
[TD]292[/TD]
[TD]19[/TD]
[TD]506[/TD]
[TD]80[/TD]
[TD]161[/TD]
[TD]41[/TD]
[TD]668[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]MARK[/TD]
[TD]531[/TD]
[TD]26[/TD]
[TD]729[/TD]
[TD]10[/TD]
[TD]259[/TD]
[TD]51[/TD]
[TD]668[/TD]
[TD]103[/TD]
[TD]1042[/TD]
[TD]136[/TD]
[TD]1073[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]JAMES[/TD]
[TD]1004[/TD]
[TD]101[/TD]
[TD]179[/TD]
[TD]62[/TD]
[TD]304[/TD]
[TD]114[/TD]
[TD]1080[/TD]
[TD]60[/TD]
[TD]683[/TD]
[TD]21[/TD]
[TD]766[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PAUL[/TD]
[TD]475[/TD]
[TD]7[/TD]
[TD]370[/TD]
[TD]7[/TD]
[TD]644[/TD]
[TD]63[/TD]
[TD]499[/TD]
[TD]82[/TD]
[TD]869[/TD]
[TD]66[/TD]
[TD]884[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]PETER[/TD]
[TD]281[/TD]
[TD]125[/TD]
[TD]34[/TD]
[TD]68[/TD]
[TD]248[/TD]
[TD]98[/TD]
[TD]844[/TD]
[TD]64[/TD]
[TD]1227[/TD]
[TD]112[/TD]
[TD]729[/TD]
[TD]131[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]ANDREW[/TD]
[TD]967[/TD]
[TD]111[/TD]
[TD]142[/TD]
[TD]97[/TD]
[TD]684[/TD]
[TD]27[/TD]
[TD]964[/TD]
[TD]59[/TD]
[TD]92[/TD]
[TD]135[/TD]
[TD]506[/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]DANIEL[/TD]
[TD]369[/TD]
[TD]78[/TD]
[TD]43[/TD]
[TD]85[/TD]
[TD]1051[/TD]
[TD]3[/TD]
[TD]1142[/TD]
[TD]22[/TD]
[TD]183[/TD]
[TD]73[/TD]
[TD]1205[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]ROBERT[/TD]
[TD]951[/TD]
[TD]84[/TD]
[TD]373[/TD]
[TD]2[/TD]
[TD]371[/TD]
[TD]108[/TD]
[TD]786[/TD]
[TD]61[/TD]
[TD]1126[/TD]
[TD]81[/TD]
[TD]733[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]GRAHAM[/TD]
[TD]607[/TD]
[TD]62[/TD]
[TD]706[/TD]
[TD]27[/TD]
[TD]266[/TD]
[TD]126[/TD]
[TD]721[/TD]
[TD]110[/TD]
[TD]52[/TD]
[TD]30[/TD]
[TD]973[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]SIMON[/TD]
[TD]836[/TD]
[TD]45[/TD]
[TD]605[/TD]
[TD]77[/TD]
[TD]802[/TD]
[TD]75[/TD]
[TD]488[/TD]
[TD]100[/TD]
[TD]329[/TD]
[TD]23[/TD]
[TD]1058[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]MARK[/TD]
[TD]906[/TD]
[TD]35[/TD]
[TD]593[/TD]
[TD]111[/TD]
[TD]331[/TD]
[TD]9[/TD]
[TD]555[/TD]
[TD]76[/TD]
[TD]702[/TD]
[TD]114[/TD]
[TD]458[/TD]
[TD]134[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]JAMES[/TD]
[TD]162[/TD]
[TD]12[/TD]
[TD]78[/TD]
[TD]109[/TD]
[TD]624[/TD]
[TD]49[/TD]
[TD]454[/TD]
[TD]7[/TD]
[TD]375[/TD]
[TD]136[/TD]
[TD]110[/TD]
[TD]129[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]PAUL[/TD]
[TD]478[/TD]
[TD]10[/TD]
[TD]560[/TD]
[TD]19[/TD]
[TD]595[/TD]
[TD]9[/TD]
[TD]1145[/TD]
[TD]1[/TD]
[TD]162[/TD]
[TD]98[/TD]
[TD]764[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]PETER[/TD]
[TD]98[/TD]
[TD]63[/TD]
[TD]360[/TD]
[TD]49[/TD]
[TD]345[/TD]
[TD]46[/TD]
[TD]1051[/TD]
[TD]57[/TD]
[TD]128[/TD]
[TD]56[/TD]
[TD]317[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]ANDREW[/TD]
[TD]1202[/TD]
[TD]87[/TD]
[TD]682[/TD]
[TD]135[/TD]
[TD]69[/TD]
[TD]35[/TD]
[TD]1024[/TD]
[TD]106[/TD]
[TD]1213[/TD]
[TD]51[/TD]
[TD]269[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]DANIEL[/TD]
[TD]787[/TD]
[TD]61[/TD]
[TD]1047[/TD]
[TD]2[/TD]
[TD]555[/TD]
[TD]98[/TD]
[TD]224[/TD]
[TD]0[/TD]
[TD]1072[/TD]
[TD]82[/TD]
[TD]841[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]ROBERT[/TD]
[TD]836[/TD]
[TD]1[/TD]
[TD]793[/TD]
[TD]26[/TD]
[TD]182[/TD]
[TD]95[/TD]
[TD]832[/TD]
[TD]120[/TD]
[TD]1012[/TD]
[TD]90[/TD]
[TD]925[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]GRAHAM[/TD]
[TD]456[/TD]
[TD]96[/TD]
[TD]965[/TD]
[TD]87[/TD]
[TD]1154[/TD]
[TD]69[/TD]
[TD]797[/TD]
[TD]41[/TD]
[TD]884[/TD]
[TD]1[/TD]
[TD]185[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]SIMON[/TD]
[TD]709[/TD]
[TD]129[/TD]
[TD]799[/TD]
[TD]86[/TD]
[TD]509[/TD]
[TD]84[/TD]
[TD]51[/TD]
[TD]79[/TD]
[TD]947[/TD]
[TD]87[/TD]
[TD]1228[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]MARK[/TD]
[TD]1225[/TD]
[TD]38[/TD]
[TD]911[/TD]
[TD]44[/TD]
[TD]248[/TD]
[TD]11[/TD]
[TD]878[/TD]
[TD]113[/TD]
[TD]1138[/TD]
[TD]30[/TD]
[TD]1125[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]JAMES[/TD]
[TD]1192[/TD]
[TD]93[/TD]
[TD]695[/TD]
[TD]116[/TD]
[TD]1199[/TD]
[TD]49[/TD]
[TD]672[/TD]
[TD]130[/TD]
[TD]190[/TD]
[TD]6[/TD]
[TD]699[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]PAUL[/TD]
[TD]242[/TD]
[TD]134[/TD]
[TD]559[/TD]
[TD]44[/TD]
[TD]437[/TD]
[TD]36[/TD]
[TD]806[/TD]
[TD]36[/TD]
[TD]1036[/TD]
[TD]71[/TD]
[TD]1082[/TD]
[TD]104[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]PETER[/TD]
[TD]1102[/TD]
[TD]118[/TD]
[TD]89[/TD]
[TD]113[/TD]
[TD]151[/TD]
[TD]5[/TD]
[TD]33[/TD]
[TD]19[/TD]
[TD]845[/TD]
[TD]134[/TD]
[TD]1018[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]ANDREW[/TD]
[TD]7[/TD]
[TD]58[/TD]
[TD]479[/TD]
[TD]133[/TD]
[TD]337[/TD]
[TD]54[/TD]
[TD]825[/TD]
[TD]132[/TD]
[TD]1196[/TD]
[TD]124[/TD]
[TD]294[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]DANIEL[/TD]
[TD]102[/TD]
[TD]91[/TD]
[TD]365[/TD]
[TD]68[/TD]
[TD]521[/TD]
[TD]31[/TD]
[TD]433[/TD]
[TD]76[/TD]
[TD]440[/TD]
[TD]12[/TD]
[TD]1175[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD][/TD]
[TD]S.V. TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]GROUP 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]SIMON[/TD]
[TD]10698[/TD]
[TD="colspan: 8"]=SUMPRODUCT((E32=$B$4:$B$28)*("SALES VALUE"=$C$3:$N$3)*$C$4:$N$28)[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]GROUP 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]MARK[/TD]
[TD]13372[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]GROUP 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]PETER[/TD]
[TD]8900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]GROUP 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]DANIEL[/TD]
[TD]11555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]SIMON[/TD]
[TD]GROUP 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]MARK[/TD]
[TD]GROUP 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]JAMES[/TD]
[TD]GROUP 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]PAUL[/TD]
[TD]GROUP 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]PETER[/TD]
[TD]GROUP 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]ANDREW[/TD]
[TD]GROUP 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]DANIEL[/TD]
[TD]GROUP 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]ROBERT[/TD]
[TD]GROUP 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]GRAHAM[/TD]
[TD]GROUP 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Final note, I can't simply SUM the Sales Values in a final column as the amount of data changes on a weekly basis.

Thanks for any help I can get with this.

Simon

 

[Marcelo Branco]

Maybe...

Array formula in C32 copied down
=SUM(IF(ISNUMBER(MATCH(B$4:B$28,IF(C$38:C$46=B32,B$38:B$46),0)),IF(C$3:N$3="SALES VALUE",C$4:N$28)))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[MixedUpExcel]

Maybe...

Array formula in C32 copied down
=SUM(IF(ISNUMBER(MATCH(B$4:B$28,IF(C$38:C$46=B32,B$38:B$46),0)),IF(C$3:N$3="SALES VALUE",C$4:N$28)))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

Hi Marcelo,

That wasn't what I was expecting. I thought I would have got an extension to the SUMPRODUCT formula.

That being said. Your solution works perfectly, thank you very much for getting back to me so quickly with a working solution.

Really appreciate your time on this.

If anyone else looking at this in the future comes up with a SUMPRODUCT solution, please can you let me know as it will help with my learning of this function.

Thank you again Marcelo.

Simon

 

[Marcelo Branco]

You are welcome. Glad to help

M.