Sumifs or Index match for data summary

[AHS5050]

Hello,

I want to get summary data (first table) for some GLs from a large database (2nd table) based on three criteria, PC# (col A), Acc (Col B) and Month (row # 2). I tried Sumifs and index match but couldn't make it work. I manually populated columns A and B in the 2nd table to use for Sumifs or Index match but still couldn't make it work.

Would you please help?

Thank you very much.

	Forecast
PC #	Acc	Jan (2023)	Feb (2023)	Mar (2023)	Apr (2023)	May (2023)	Jun (2023)	Jul (2023)	Aug (2023)	Sep (2023)	Oct (2023)	Nov (2023)	Dec (2023)
P0071	440500
	440600										16,249	16,249	16,249
	440010										180,493	180,493	180,493
	450000
	503300
	470010

Profit Center	GL			Date	Jul (2023)	Aug (2023)	Sep (2023)	Oct (2023)	Nov (2023)	Dec (2023)
			Profit Center	Account
P0071	IS IS		P0071 Shady Oaks 2	▾ IS IS	–	–	–	-346,055.11	-346,055.11	-346,055.11
	NET_EA			▾ NET_EAR Net Earnings	–	–	–	-346,055.11	-346,055.11	-346,055.11
	EAR_BE			▾ EAR_BEF_NCI Earnings Before NCI	–	–	–	-346,055.11	-346,055.11	-346,055.11
	EBT EB			▾ EBT EBT	–	–	–	-346,055.11	-346,055.11	-346,055.11
	EBIT E			▾ EBIT EBIT	–	–	–	-346,055.11	-346,055.11	-346,055.11
	EBITDA			▾ EBITDA EBITDA	–	–	–	31,208.93	31,208.93	31,208.93
	BG _PR			▾ BG _PRO BG Profit	–	–	–	31,208.93	31,208.93	31,208.93
	NET_RE			▾ NET_REV Net Revenue	–	–	–	308,846.35	308,846.35	308,846.35
	REV Re			▾ REV Revenue	–	–	–	308,846.35	308,846.35	308,846.35
	NON_RE			▾ NON_REG_ENE_REV Non Regulated Energy Revenue	–	–	–	196,741.46	196,741.46	196,741.46
	CON_EN			▾ CON_ENE_REV Contracted Energy Revenue	–	–	–	180,492.70	180,492.70	180,492.70
	440010			440010 PPA	–	–	–	180,492.70	180,492.70	180,492.70
	CAP_RE			▾ CAP_REV_CON Capacity Revenue - Contracts	–	–	–	0.01	0.01	0.01
	440500			▾ 440500 Contracts - Capacity	–	–	–	0.01	0.01	0.01
	CAP_RE			▾ CAP_REV_REA Capacity Revenue - Reactive	–	–	–	16,248.75	16,248.75	16,248.75
	440600			440600 Reactive - Capacity	–	–	–	16,248.75	16,248.75	16,248.75
	OTH_RE			▾ OTH_REV Other Revenue	–	–	–	112,104.89	112,104.89	112,104.89
	REC_RE			▾ REC_REV_CON REC Revenue - Contracts	–	–	–	112,104.89	112,104.89	112,104.89
	450000			450000 Contracts - REC	–	–	–	112,104.89	112,104.89	112,104.89
	OPEX O			▾ OPEX OPEX	–	–	–	277,637.42	277,637.42	277,637.42
	SIT_OV			▾ SIT_OVE Site Overhead	–	–	–	277,637.42	277,637.42	277,637.42
	MIS_OT			▾ MIS_OTH_DED Miscellaneous Other Deductions	–	–	–	277,637.42	277,637.42	277,637.42
	503300			503300 Miscellaneous Other Deductions	–	–	–	277,637.42	277,637.42	277,637.42

 

[Misca]

Fill down the PC#s in both tables and your problem becomes a lot easier to solve:

Mr Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Forecast
2	PC #	Acc	Apr (2023)	May (2023)	Jun (2023)	Jul (2023)	Aug (2023)	Sep (2023)	Oct (2023)	Nov (2023)	Dec (2023)
3
4
5	P0071	440500	-	-	-	-	-	-	0,01	0,01	0,01
6	P0071	440600	-	-	-	-	-	-	16 248,75	16 248,75	16 248,75
7	P0071	440010	-	-	-	-	-	-	180 492,70	180 492,70	180 492,70
8	P0071	450000	-	-	-	-	-	-	112 104,89	112 104,89	112 104,89
9	P0071	503300	-	-	-	-	-	-	277 637,42	277 637,42	277 637,42
10	P0071	470010	-	-	-	-	-	-	-	-	-
11
12
13	Profit Center	GL			Date	Jul (2023)	Aug (2023)	Sep (2023)	Oct (2023)	Nov (2023)	Dec (2023)
14				Profit Center	Account
15	P0071	IS IS		P0071 Shady Oaks 2	▾ IS IS	-	-	-	- 346 055,11	- 346 055,11	- 346 055,11
16	P0071	NET_EA			▾ NET_EAR Net Earnings	-	-	-	- 346 055,11	- 346 055,11	- 346 055,11
17	P0071	EAR_BE			▾ EAR_BEF_NCI Earnings Before NCI	-	-	-	- 346 055,11	- 346 055,11	- 346 055,11
18	P0071	EBT EB			▾ EBT EBT	-	-	-	- 346 055,11	- 346 055,11	- 346 055,11
19	P0071	EBIT E			▾ EBIT EBIT	-	-	-	- 346 055,11	- 346 055,11	- 346 055,11
20	P0071	EBITDA			▾ EBITDA EBITDA	-	-	-	31 208,93	31 208,93	31 208,93
21	P0071	BG _PR			▾ BG _PRO BG Profit	-	-	-	31 208,93	31 208,93	31 208,93
22	P0071	NET_RE			▾ NET_REV Net Revenue	-	-	-	308 846,35	308 846,35	308 846,35
23	P0071	REV Re			▾ REV Revenue	-	-	-	308 846,35	308 846,35	308 846,35
24	P0071	NON_RE			▾ NON_REG_ENE_REV Non Regulated Energy Revenue	-	-	-	196 741,46	196 741,46	196 741,46
25	P0071	CON_EN			▾ CON_ENE_REV Contracted Energy Revenue	-	-	-	180 492,70	180 492,70	180 492,70
26	P0071	440010			440010 PPA	-	-	-	180 492,70	180 492,70	180 492,70
27	P0071	CAP_RE			▾ CAP_REV_CON Capacity Revenue - Contracts	-	-	-	0,01	0,01	0,01
28	P0071	440500			▾ 440500 Contracts - Capacity	-	-	-	0,01	0,01	0,01
29	P0071	CAP_RE			▾ CAP_REV_REA Capacity Revenue - Reactive	-	-	-	16 248,75	16 248,75	16 248,75
30	P0071	440600			440600 Reactive - Capacity	-	-	-	16 248,75	16 248,75	16 248,75
31	P0071	OTH_RE			▾ OTH_REV Other Revenue	-	-	-	112 104,89	112 104,89	112 104,89
32	P0071	REC_RE			▾ REC_REV_CON REC Revenue - Contracts	-	-	-	112 104,89	112 104,89	112 104,89
33	P0071	450000			450000 Contracts - REC	-	-	-	112 104,89	112 104,89	112 104,89
34	P0071	OPEX O			▾ OPEX OPEX	-	-	-	277 637,42	277 637,42	277 637,42
35	P0071	SIT_OV			▾ SIT_OVE Site Overhead	-	-	-	277 637,42	277 637,42	277 637,42
36	P0071	MIS_OT			▾ MIS_OTH_DED Miscellaneous Other Deductions	-	-	-	277 637,42	277 637,42	277 637,42
37	P0071	503300			503300 Miscellaneous Other Deductions	-	-	-	277 637,42	277 637,42	277 637,42
Sheet3

Cell Formulas
Range	Formula
C5:J10	C5	=IFNA(INDEX($F$15:$K$37,MATCH($A5&$B5,$A$15:$A$37&$B$15:$B$37,0),MATCH(C$2,$F$13:$K$13,0)),0)
K5:K10	K5	=SUMIFS(INDEX($F$15:$K$37,,MATCH(K$2,$F$13:$K$13,0)),$A$15:$A$37,$A5,$B$15:$B$37,$B5)
A6:A10,A16:A37	A6	=A5

I'd prefer using the index / match all the way but since you had tried the sumifs as well I decided to use that in the Dec column just to show that sometimes the sumifs is not the easiest way to solve things.
However, if you want to find out the best way to solve your problem try using Power Query to unpivot your date columns of your lookup table ( select the "constant" columns and use the unpivot other columns to make sure the query adapts to new columns in your data source ). That way your date columns become a similar single column and all your values can be found in a single column as well. That is the "correct" way to store data to be used in calculations. That way your sumifs would become a lot easier - or better yet create a simple pivot table and you don't even need the formulas anymore:

Profit Center	GL	Date	Value		Sum of Value		Date
P0071	IS IS	Oct (2023)	- 346 055,11		Profit Center	GL	Dec (2023)	Nov (2023)	Oct (2023)	Grand Total
P0071	IS IS	Nov (2023)	- 346 055,11		P0071	440010	180492,7	180492,7	180492,7	541478,1
P0071	IS IS	Dec (2023)	- 346 055,11			440500	0,01	0,01	0,01	0,03
P0071	NET_EA	Oct (2023)	- 346 055,11			440600	16248,75	16248,75	16248,75	48746,25
P0071	NET_EA	Nov (2023)	- 346 055,11			450000	112104,89	112104,89	112104,89	336314,67
P0071	NET_EA	Dec (2023)	- 346 055,11			503300	277637,42	277637,42	277637,42	832912,26
P0071	EAR_BE	Oct (2023)	- 346 055,11		P0071 Total		586483,77	586483,77	586483,77	1759451,31
P0071	EAR_BE	Nov (2023)	- 346 055,11		Grand Total		586483,77	586483,77	586483,77	1759451,31

The best thing about the Power Query solution is you could keep your source data in other file(s) and whenever you get new data / new files / new products etc. all you need to do is click the Refresh All button and your pivot table would be up to date.

 

[AHS5050]

Thank you Misca for your help. Appreciate it. It worked.