SUMIFS INDEX and MATCH

[atearth]

Hi all,

I'm trying to use SUMIFS INDEX and MATCH but the answer I get is wrong.
Anyone know what the answer is to a problem I have?
Please see the image below.
Thank you in advance

=SUMIFS(INDEX($F$15:$M$18,0,MATCH(F$24,$F$14:$M$14,0)),$D$15:$D$18,$D25)

 

[awoohaw]

does anything in rows 3 thru 12 have anything to do with your calculation in H25? I htink not. I just want to be sure I have not overlooked anything.

 

[atearth]

Row 3 to 12 is just background data.

Has no use in this situation

 

[AhoyNC]

Maybe something like this.
Change ranges to match your data.

Book2
	D	E	F	G	H	I	J
1
2	WBS Codes	Jan-23	Jan-23	Jan-23	Jan-23	Feb-23	Feb-23
3	100	450				450
4	101		675				675
5	102			900			900
6	103	225.00	225.00	225.00	225.00	225.00	225.00
7
8
9
10	WBS Codes	Jan-23	Feb-23
11	100	450	450
12	101	675	675
13	102	900	900
14	103	900	450
Sheet3

Cell Formulas
Range	Formula
E11:F14	E11	=SUMPRODUCT(--($D11=$D$3:$D$6)*--($E$2:$J$2=E$10)*$E$3:$J$6)

 

[awoohaw]

ignore this. message. i made errors.

 

[awoohaw]

Here is my version of it.
Comment regarding the dates: The column header format needs to match the lookup format. If you have date values on the header row, the lookup must also be a date value. You can have different days of the month in the header. The formula only looks at the month. However, if you put multiple years then you'llneed to tweak the formula to get the correct year. Or you can have text inboth. but it should match.

Book1
	D	E	F	G	H	I	J	K	L	M
14			Jan 23	Jan 23	Jan 23	Jan 23	Feb 23	Feb 23	Feb 23	Feb 23
15	100		450	0	0	0	450	0	0	0
16	101		0	675	0	0	0	675	0	0
17	102		0	0	900	0	0	0	900	0
18	103		225	225	225	225	225	225	225	225
19
20
21			Jan 23
22	103		900
Sheet3

Cell Formulas
Range	Formula
F22	F22	=SUMPRODUCT(INDEX($F$15:$M$18,MATCH($D22,$D$15:$D$18,0),1):INDEX($F$15:$M$18,MATCH($D22,$D$15:$D$18,0),COLUMNS($F$14:$M$14)), (--(MONTH($F$21)=MONTH($F$14:$M$14))))

 

[atearth]

@AhoyNC @awoohaw
Thank you both for trying but I was lead to believe that doing this via SUMPRODUCT instead of SUMIFS INDEX and MATCH impacts the performance of an excel file.
Wondering what a SUMIFS INDEX and MATCH version of this would look like.

 

[awoohaw]

@AhoyNC @awoohaw
Thank you both for trying but I was lead to believe that doing this via SUMPRODUCT instead of SUMIFS INDEX and MATCH impacts the performance of an excel file.
Wondering what a SUMIFS INDEX and MATCH version of this would look like.

How big is your workbook? If it is small I would not worry.

 

[atearth]

At the moment 3MB, just that I know it will end up being 10MB plus soon.
As I will be using the formula in another document.

The formula that @AhoyNC created works just that I am using it in 54,000 cells.

And saving the document now takes ages.

Calculating 8 threads.
So hope to find a SUMIFS INDEX and MATCH instead of SUMPRODUCT

 

[awoohaw]

This is without SUMPRODUCT or SUMIF.

Book1
	D	E	F	G	H	I	J	K	L	M
14			Jan 23	Jan 23	Jan 23	Jan 23	Feb 23	Feb 23	Feb 23	Feb 23
15	100		450	0	0	0	450	0	0	0
16	101		0	675	0	0	0	675	0	0
17	102		0	0	900	0	0	0	900	0
18	103		225	225	225	225	225	225	225	225
19
20
21			Jan 23	2/1/2023
22	100		450	450
23	101		675	675
24	102		900	900
25	103		900	900
26
sumproduct on grid

Cell Formulas
Range	Formula
F22:G25	F22	=SUM(INDEX($F$15:$M$18,MATCH($D22,$D$15:$D$18,0),1): INDEX($F$15:$M$18,MATCH($D22,$D$15:$D$18,0),COLUMNS($F$14:$M$14)) *(--MONTH(F$21)=MONTH($F$14:$M$14)))

 

[AhoyNC]

With SUMIFS your ranges need to be the same size which yours are not.
Here is an option with FILTER function (Excel 365)

Book2
	D	E	F	G	H	I	J
1
2	WBS Codes	Jan-23	Jan-23	Jan-23	Jan-23	Feb-23	Feb-23
3	100	450				450
4	101		675				675
5	102			900			900
6	103	225.00	225.00	225.00	225.00	225.00	225.00
7
8
9
10	WBS Codes	Jan-23	Feb-23
11	100	450	450
12	101	675	675
13	102	900	900
14	103	900	450
Sheet3

Cell Formulas
Range	Formula
E11:E14	E11	=SUM(FILTER(FILTER($E$3:$J$6,(E$2:J$2=E$10)),$D11=$D$3:$D$6))
F11:F14	F11	=SUM(FILTER(FILTER($E$3:$J$6,(E$2:J$2=F$10)),$D11=$D$3:$D$6))