Combine Index Match Sumproduct

[*shudder*]

Hi Guys,

I am looking to sum some data based on criteria in two columns and a row header. Example of data layout as below.

Date	Customer	Product 1	Product 2	Product 3	Product 4
01/01/2020​	A	100​	1000​	2000​	3000​
01/01/2021​	A	100​	1000​	2000​	3000​
01/01/2022​	A	100​	1000​	2000​	3000​
01/01/2022​	A	200​	1000​	2000​	3000​
01/01/2020​	B	100​	1000​	2000​	3000​
01/01/2021​	B	100​	1000​	2000​	3000​
01/01/2022​	B	100​	1000​	2000​	3000​
01/01/2020​	C	100​	1000​	2000​	3000​
01/01/2021​	C	100​	1000​	2000​	3000​
01/01/2022​	C	100​	1000​	2000​	3000​

This is the result I am looking for:

Date:	01/01/2022
Customer:	A
Product:	Product 1
Result	300

This is the index/ match formula I currently have which only returns 200 rather than suming 100+200:

{=INDEX(Table1[[Product 1]:[Product 4]],MATCH(B15,IF(Table1[Customer]=B16,Table1[Date]),1),MATCH(B17,Table1[[#Headers],[Product 1]:[Product 4]],0))}

Thanks in advance.

 

[AlanY]

try this

Book1
	A	B	C	D	E	F
1	Date	Customer	Product 1	Product 2	Product 3	Product 4
2	01/01/2020	A	100	1000	2000	3000
3	01/01/2021	A	100	1000	2000	3000
4	01/01/2022	A	100	1000	2000	3000
5	01/01/2022	A	200	1000	2000	3000
6	01/01/2020	B	100	1000	2000	3000
7	01/01/2021	B	100	1000	2000	3000
8	01/01/2022	B	100	1000	2000	3000
9	01/01/2020	C	100	1000	2000	3000
10	01/01/2021	C	100	1000	2000	3000
11	01/01/2022	C	100	1000	2000	3000
12
13	This is the result I am looking for:
14
15	Date:	01/01/2022
16	Customer:	A
17	Product:	Product 1
18	Result	300
288

Cell Formulas
Range	Formula
B18	B18	=SUMPRODUCT((B17=C1:F1)*(A2:A11=B15)*(B16=B2:B11),C2:F11)

 

[*shudder*]

Thanks Alan, works perfectly.

 

[AlanY]

you're welcome