Formula Help: INDEX vs SUMIFS vs VLOOKUP

[excel?]

Need your help with a formula that will provide me the total based on certain criteria out of a large database of data.

Data looks like this (sorry unable to paste a snapshot so rough details below):

Year Month Week Day 101 102 103 104 105 106 Etc...
2022 1 1 Mon 21 18 12 35 0 18
2022 1 1 Tues 5 20 17 28 2 0
2022 1 1 Wed 13 11 14 31 7 9
etc... Thu - Sun
2022 1 2 Mon 16 19 21 22 18 12
2022 1 2 Tues 19 15 28 30 13 20
2022 1 2 Wed 17 17 23 14 16 18
etc... Thu - Sun
etc... Jan - Dec / Weeks 1-52

I am needing a formula that will SUM all the amounts for each Column (101, 102, 103, etc,) by Week (1, 2, 3, etc.). Example with the data provided: Week 2 for 101 = 52 (16 + 19 + 17)

I can't get SUMIFS to work with horizontal and vertical criteria. I'm not sure how to combine VLOOKUP with HLOOKUP to give me the amounts and I am unfamiliar with INDEX to create a formula that would achieve the results I need.

Would really appreciate your expertise for a formula that would look at my data and give me the the Total by Week for each Column.

Thank you.

 

[Fluff]

Can you post some sample data. Along with where & how you want the result to look

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jtakw]

Hi,

May be something like this:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Year	Month	Week	Day	101	102	103	104	105	106		Week	101	102	103	104	105	106
2	2022	1	1	Mon	21	18	12	35	0	18		1	39	49	43	94	9	27
3	2022	1	1	Tues	5	20	17	28	2	0		2	52	51	72	66	47	50
4	2022	1	1	Wed	13	11	14	31	7	9
5	2022	1	2	Mon	16	19	21	22	18	12
6	2022	1	2	Tues	19	15	28	30	13	20
7	2022	1	2	Wed	17	17	23	14	16	18
Sheet1070

Cell Formulas
Range	Formula
M2:R3	M2	=SUMPRODUCT(($C$2:$C$10=$L2)*($E$1:$J$1=M$1)*$E$2:$J$10)

 

[excel?]

Perfect - the =SUMPRODUCT(($C$2:$C$10=$L2)*($E$1:$J$1=M$1)*$E$2:$J$10) Formula is EXACTLY what I was needing. Thank you so very much!!!!

 

[jtakw]

You're welcome, thanks for the feedback.