Index/Match formula or other method?

[ac7]

Hello -

I am unsure of the best way to accomplish what I am looking for. I have 2 worksheets, 1 labeled as 'Data' and the other as 'Results'. Here is the info on the 'Data' worksheet:

	A​	B​	C​	D​	E​	F​
1​	Box	100	101	102	103	104
2​	Date Completed	9/1/2023	9/15/2023	10/5/2023	10/12/2023	11/3/2023
3​	Option 1	1	1	1	1	1
4​	Option 2		1		1
5​	Option 3	1		1		1
6​	Option 4		1

On the 'Results' worksheet, I need to find the matching option number on the 'Data' worksheet and then sum the number of times that option was used for a particular month.

	A​	B​	C​	D​
1​		Sept	Oct	Nov
2​	Option 1	2	2	1
3​	Option 2	1	1	0
4​	Option 3	1	1	1
5​	Option 4	1	0	0

I am struggling with the best way to do this. Any help would be appreciated.

 

[DanteAmor]

Data sheet:

Dante Amor
	A	B	C	D	E	F
1	Box	100	101	102	103	104
2	Date Completed	01/sep/2023	15/sep/2023	01/oct/2023	15/oct/2023	03/nov/2023
3	Option 1	1	1	1	1	1
4	Option 2		1		1
5	Option 3	1		1		1
6	Option 4		1
Data

Consider the following:
In row 1 of the result sheet, you must put 3 letters of each month as shown in the following example:
Result sheet:

Dante Amor
	A	B	C	D
1		Sep	Oct	Nov
2	Option 1	2	2	1
3	Option 2	1	1	0
4	Option 3	1	1	1
5	Option 4	1	0	0
Results

Cell Formulas
Range	Formula
B2:D5	B2	=SUMPRODUCT((Data!$A$3:$A$6=$A2)*(TEXT(Data!$B$2:$F$2,"mmm")=LOWER(B$1))*(Data!$B$3:$F$6))

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[ac7]

That worked, thank you!

 

[Peter_SSs]

Given that you have excel 365, another approach could be this (using the same layout as Dante)

ac7.xlsm
	A	B	C	D
1		Sep	Oct	Nov
2	Option 1	2	2	1
3	Option 2	1	1	0
4	Option 3	1	1	1
5	Option 4	1	0	0
Results

Cell Formulas
Range	Formula
B2:D5	B2	=SUM(FILTER(FILTER(Data!$B$3:$F$6,Data!$A$3:$A$6=$A2),TEXT(Data!$B$2:$F$2,"mmm")=B$1))

BTW, there is no need for the LOWER function in Dante's suggestion. This would work just as well.

=SUMPRODUCT((Data!$A$3:$A$6=$A2)*(TEXT(Data!$B$2:$F$2,"mmm")=B$1)*(Data!$B$3:$F$6))