Sumifs and Index match match

[CascadeDiver]

Hello,

I'm having a difficult time writing a formula that acts like Index match match along with sumifs.

I have a table with a column of dates and 4 columns with names. To the right of each name column is a number.
My end goal is to sum the numbers adjacent to each name and date. Those numbers are then placed in a different table that has Dates, Names, and Sums.

In the example I have some dates skipped and some dates duplicated. Shown in the right graph is the desired outcome.

I've tried doing a Power Query to achieve these results however I got a bit lost in that attempt.

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	Date	Person 1	P1#	Person 2	P2#	Person 3	P3#	Person 4	P4#		Date	James	Lucas	Benjamin	Mason	Oliver	Liam	Elijah	William	Noah	Ethan
3	10/1/2020	Liam	10	Lucas	8	Oliver	3	Mason	3		10/1/2020		8		3	3	10
4	10/2/2020	Noah	6	Ethan	2	Lucan	5	William	6		10/2/2020								6	6	2
5	10/3/2020	Oliver	4	Liam	1	Elijah	7	Lucas	1		10/3/2020		1			4	1	7
6	10/4/2020	Willaim	9	Elijah	9	Mason	9	Noah	2		10/4/2020				19	4		10		2	10
7	10/4/2020	Elijah	1	Mason	10	Ethan	10	Oliver	4		10/5/2020
8	10/6/2020	Mason	5	Benjamin	7	Noah	4	James	7		10/6/2020	7		7	5					4
9	10/7/2020	Benjamin	8	James	6	Liam	8	Elijah	9		10/7/2020	6		8			8	9
10	10/8/2020	Lucas	3	Noah	3	William	6	Benjamin	10		10/8/2020	2	3	12					11	3	6
11	10/8/2020	James	2	William	5	Benjamin	2	Ethan	6		10/9/2020
12	10/10/2020	Ethan	7	Oliver	4	James	1	Liam	5		10/10/2020	1				4	5				7
Sheet5

Cell Formulas
Range	Formula
L3:U12	L3	=SUM(IF($A$3:$A$12=$K3,IF($B$3:$H$12=L$2,$C$3:$I$12)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Also, please consider using the XL2BB add-in to display your sample sheet. See the link in the response box. It makes it much easier for the helpers to start working with your data.

 

[CascadeDiver]

Yes that certainly worked. I had 4 different sumifs going thinking there has got to be an easier way.

I will take a look at xl2bb .

Thank you very much

 

[CascadeDiver]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	Date	Person 1	P1#	Person 2	P2#	Person 3	P3#	Person 4	P4#		Date	James	Lucas	Benjamin	Mason	Oliver	Liam	Elijah	William	Noah	Ethan
3	10/1/2020	Liam	10	Lucas	8	Oliver	3	Mason	3		10/1/2020		8		3	3	10
4	10/2/2020	Noah	6	Ethan	2	Lucan	5	William	6		10/2/2020								6	6	2
5	10/3/2020	Oliver	4	Liam	1	Elijah	7	Lucas	1		10/3/2020		1			4	1	7
6	10/4/2020	Willaim	9	Elijah	9	Mason	9	Noah	2		10/4/2020				19	4		10		2	10
7	10/4/2020	Elijah	1	Mason	10	Ethan	10	Oliver	4		10/5/2020
8	10/6/2020	Mason	5	Benjamin	7	Noah	4	James	7		10/6/2020	7		7	5					4
9	10/7/2020	Benjamin	8	James	6	Liam	8	Elijah	9		10/7/2020	6		8			8	9
10	10/8/2020	Lucas	3	Noah	3	William	6	Benjamin	10		10/8/2020	2	3	12					11	3	6
11	10/8/2020	James	2	William	5	Benjamin	2	Ethan	6		10/9/2020
12	10/10/2020	Ethan	7	Oliver	4	James	1	Liam	5		10/10/2020	1				4	5				7
Sheet5

Cell Formulas
Range	Formula
L3:U12	L3	=SUM(IF($A$3:$A$12=$K3,IF($B$3:$H$12=L$2,$C$3:$I$12)))
Press CTRL+SHIFT+ENTER to enter array formulas.

I'm struggling changing this formula around to fit a slightly different parameter. How would I go about changing to Summing everything in the month and year?

I actually have this done already except I needed to make a helper column that displays the month of the date. I am trying to avoid the helper column. So in the case the K column displayed the words January - December.

I did find something helpful for a countifs but I haven't been successful at adapting it
=COUNTIFS(Table1[Date],">="&[@Month],Table1[Date],"<="&EOMONTH([@Month],0)
The Month column is a date 10/1/2020 formatted to ready October to provide the month name as a visual