Index Match formula for Google Sheets

[jimbogarner]

Hi,

I'm trying to use an index match formula that looks up multiple criteria in a grid, however, the issue I have is that there will be multiple lines that meet the same criteria and I want it to sum them all up? So for example I have the following table in a tab called TM1 Data Dump

I have the below formula in a different sheet;
=-INDEX(TM1_Data_Dump!$F$13:$P$2298,MATCH(1,(TM1_Data_Dump!$A$13:$A$2298=$D4)*(TM1_Data_Dump!$B$13:$B$2298=$B$6)*(TM1_Data_Dump!$D$13:$D$2298=$C6),0),MATCH(1,(TM1_Data_Dump!$F$11:$P$11=D$2)*(TM1_Data_Dump!$F$12:$P$12=D$3)))

The issue is, I need it to look up the quarter but as the data is retrieved by month it doesn't sum the 3 entries per quarter up... the other issue is, this is per our financial year, so Q1=Jul-Sep...

Edit - forgot to add the snip from the second tab the formula is in;

Thanks,

James

 

[Fluff]

Is this for Google Sheets or Excel?

 

[jimbogarner]

Oh sorry apologies its in google sheets!

 

[jimbogarner]

This might, with hindsight be easier with a SUM IF, but the column it sums from is dynamic based on the last 2 selections, IE GL Actual/2021 for example.... I just don't know how to do this

 

[Fluff]

Oh sorry apologies its in google sheets!

In that case you need to clearly state that in your op, or title & such questions should be posted to the General Discussion & Other Applications section of the board.
I have done that for you this time.

 

[Fluff]

If you want to sum all matching rows, then you need to use sumifs, or the filter function.

 

[jimbogarner]

How do I make the sum if column dynamic? IE for GL Actual/2021 it would sum up column H, however, GL Actual, 2022 would sum up column I etc....

 

[DanteAmor]

Use sumproduct.
For example:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
10
11						GL Actual	GL Actual	GL Actual	GL Actual	GL Actual	Forecast Q1	Forecast Q2	LRP	LRP
12	Qtr	Cat a	week	Reless	Cost	2019	2020	2021	2022	2023	2023	2023	2024	2025
13	Q4	a	Jul	Airtime		93,758	12,572	36,959	50,902	19,842	10,885	11,675	58,225	74,632
14	Q4	a	Jul	Airtime		89,026	15,556	90,063	48,744	35,123	26,125	1,207	24,737	85,514
15	Q4	a	Jul	S&P		90,325	30,716	51,263	11,342	91,935	16,575	13,094	21,096	75,770
16	Q4	a	Jul	S&P		84,652	69,646	38,429	35,963	83,653	94,823	42,773	55,279	87,892
17	Q4	a	Jul	Airtime		4,452	88,201	63,232	65,439	82,648	57,123	91,721	39,506	44,648
TM1_Data_Dump

FORMULA

Dante Amor
	A	B	C	D	E	F	G	H
1
2				GL Actual	GL Actual	GL Actual	Forecast Q1	Forecast Q2
3			Year	2019	2020	2021	2023	2023
4			Period	Q4	Q4	Q4	Q4	Q4
5
6		a	Airtime	187,236	116,329	190,254	94,133	104,603
7		a	S&P	174,977	100,362	89,692	111,398	55,867
Sheet2

Cell Formulas
Range	Formula
D6:H7	D6	=SUMPRODUCT((TM1_Data_Dump!$A$13:$A$3000=D$4)*(TM1_Data_Dump!$B$13:$B$3000=$B6)*(TM1_Data_Dump!$D$13:$D$3000=$C6)*(TM1_Data_Dump!$F$11:$P$11=D$2)*(TM1_Data_Dump!$F$12:$P$12=D$3)*(TM1_Data_Dump!$F$13:$P$3000))

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
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.

 

[Fluff]

How do I make the sum if column dynamic?

In much the same way as you did in the formula you posted

=FILTER(FILTER(TM1_Data_Dump!$F$13:$P$2298,(TM1_Data_Dump!$A$13:$A$2298=$D4)*(TM1_Data_Dump!$B$13:$B$2298=$B$6)*(TM1_Data_Dump!$D$13:$D$2298=$C6)),(TM1_Data_Dump!$F$11:$P$11=D$2)*(TM1_Data_Dump!$F$12:$P$12=D$3))

 

[jimbogarner]

magnificent, thank you so much!