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.

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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.