Sumproduct for qtrly data

[Holger]

Hi there,

I have a table that essentially has a few naming columns and then:

*Col A-K = customer, KPI, location......etc.
* Col L = monthly target column
* Col M-Y = monthly actual columns
* rows are different KPI, customers etc.

I want to calculate how many KPI in a month or qtr or YTD are >= target and summarise as a % achievement.

I got the above working for monthly calculations as follows:

How many targets: = 121 =SUMPRODUCT(--(TRIM(INDEX(KPIRawDataCL!A5:Y125,,MATCH($B$5,KPIRawDataCL!A2:Y2,0)))<>"")) // $B$5 user selects month
For a month >= target: = 91 =SUMPRODUCT(--(INDEX(KPIRawDataCL!A5:Y125,,MATCH($B$5,KPIRawDataCL!A2:Y2,0))>=KPIRawDataCL!$L$5:$L$125),--NOT(ISBLANK(INDEX(KPIRawDataCL!A5:Y125,,MATCH($B$5,KPIRawDataCL!A2:Y2,0)))))
Achievement = 91/121 = 75%

Qtrly calcs I'm getting stuck:

How many targets: 363 = SUMPRODUCT(--(TRIM(INDEX(KPIRawDataCL!A5:Y125,,MATCH($B$5,KPIRawDataCL!A2:Y2,0)))<>""))*$B$10 // $B$5 user selects month ; $B$10 = 1,2,3 depending which month in qtr
For qtr how many are >= target = ???? I dont know how to adjust above formula to calculate for the quarter. I think the problem is that it stops at the first col for the quarter. For a quarter I use a row at the top to indicate which qtr a selected month falls in.

Screenshot below shows the data (have hidden clients and location hence KPI# repeats)

Thank You so much for any help.....

 

[Holger]

Hi Kirk,

Thanks for this again. The missing col from my live data are client name and state....... So each client can have same KPI (ie 1a) multiple times, pending how many states we service them.....
I would like to average by KPI ie 1a, 2, 3a.... So if month label 8 is chosen it would return 0.9986 which is average of M5 and M13 in data tab.

So, in the overall dashboard I would like to show:

* Monthly average achievement for a KPI across all clients, states and month etc.
* Qtrlly average achievement for a KPI across all clients, states and month etc.
* YTD average achievement for a KPI across all clients, states and month etc.

Later, on a 2nd dashboard (client detail) I would like to be able to select month&client and then show
* Monthly average achievement for each KPI - no need to split by state or other col
* Qtrlly average achievement for each KPI - no need to split by state or other col
* YTD average achievement for each KPI - no need to split by state or other col

LET Function and SUMPRODUCT.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	Select Month Label		Achievement		Count Not Blank	Count Exceeding Target		All in One
5	13		83.33%		12	10		83.33%
6
7	Select Quarter		Achievement
8	4		91.67%		36	33		91.67%
9
10	Select Mo-Yr for YTD		Achievement
11	Mar-22		91.67%		36	33		91.67%
12
13
14	Dashboard 1
15								Month	Qtr	YTD
16	KPI
17	4b							99.86%			<-- problem is, that currently KPIAverage has the month hardcoded and not taken form modata
18	5a							99.34%			If I repplace with modata I get a long array ant not average by KPI
19	7							100.00%
20	1a							100.00%
21	2							100.00%
22	3a							100.00%
23	3b							67.86%
24	4a							100.00%
25
26
27
28	Dashboard 2
29
30	Client:	A
31								Month	Qtr	YTD
32	KPI
33	4b
34	1a
35	3b
Sheet1

Cell Formulas
Range	Formula
D5	D5	=LET( modata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$2:$R$2=$B5), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((modata<>0)*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>0)), cgtetarget/cbasis )
F5	F5	=LET( modata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$2:$R$2=$B5), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((modata<>0)*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>0)), cbasis )
G5	G5	=LET( modata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$2:$R$2=$B5), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((modata<>0)*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>0)), cgtetarget )
I5	I5	=LET( rawdata, KPIRawDataCL!$F$5:$R$125, target, KPIRawDataCL!$E$5:$E$125, modata, FILTER(rawdata,KPIRawDataCL!$F$2:$R$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$F$3:$R$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$F$4:$R$4<=$B$11)*(YEAR(KPIRawDataCL!$F$4:$R$4)=YEAR($B5))), data, modata, cgtetarget, SUMPRODUCT((data<>0)*(data>=target)), cbasis, SUMPRODUCT(--(data<>0)), cgtetarget/cbasis )
D8	D8	=LET( qtrdata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$3:$R$3=$B8), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((qtrdata<>0)*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>0)), cgtetarget/cbasis )
F8	F8	=LET( qtrdata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$3:$R$3=$B8), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((qtrdata<>0)*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>0)), cbasis )
G8	G8	=LET( qtrdata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$3:$R$3=$B8), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((qtrdata<>0)*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>0)), cgtetarget )
I8	I8	=LET( rawdata, KPIRawDataCL!$F$5:$R$125, target, KPIRawDataCL!$E$5:$E$125, modata, FILTER(rawdata,KPIRawDataCL!$F$2:$R$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$F$3:$R$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$F$4:$R$4<=$B$11)*(YEAR(KPIRawDataCL!$F$4:$R$4)=YEAR($B8))), data, qtrdata, cgtetarget, SUMPRODUCT((data<>0)*(data>=target)), cbasis, SUMPRODUCT(--(data<>0)), cgtetarget/cbasis )
D11	D11	=LET( yrdata, FILTER(KPIRawDataCL!$F$5:$R$125,(KPIRawDataCL!$F$4:$R$4<=$B11)*(YEAR(KPIRawDataCL!$F$4:$R$4)=YEAR($B11))), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((yrdata<>0)*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>0)), cgtetarget/cbasis )
F11	F11	=LET( yrdata, FILTER(KPIRawDataCL!$F$5:$R$125,(KPIRawDataCL!$F$4:$R$4<=$B11)*(YEAR(KPIRawDataCL!$F$4:$R$4)=YEAR($B11))), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((yrdata<>0)*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>0)), cbasis )
G11	G11	=LET( yrdata, FILTER(KPIRawDataCL!$F$5:$R$125,(KPIRawDataCL!$F$4:$R$4<=$B11)*(YEAR(KPIRawDataCL!$F$4:$R$4)=YEAR($B11))), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((yrdata<>0)*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>0)), cgtetarget )
I11	I11	=LET( rawdata, KPIRawDataCL!$F$5:$R$125, target, KPIRawDataCL!$E$5:$E$125, modata, FILTER(rawdata,KPIRawDataCL!$F$2:$R$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$F$3:$R$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$F$4:$R$4<=$B$11)*(YEAR(KPIRawDataCL!$F$4:$R$4)=YEAR($B11))), data, yrdata, cgtetarget, SUMPRODUCT((data<>0)*(data>=target)), cbasis, SUMPRODUCT(--(data<>0)), cgtetarget/cbasis )
B17:B24	B17	=UNIQUE(KPIRawDataCL!B5:B16)
I17:I24	I17	=LET( modata, FILTER(KPIRawDataCL!$F$5:$Q$125,KPIRawDataCL!$F$2:$Q$2=B5), KPIselection,KPIRawDataCL!$B$5:$B$125, KPIaverage, AVERAGEIFS(KPIRawDataCL!$F$5:$F$125, KPIselection, B17#), KPIaverage)
B33:B35	B33	=UNIQUE(FILTER(KPIRawDataCL!B5:B16,KPIRawDataCL!C5:C16=C30))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B8	List	1,2,3,4
B5	List	=KPIRawDataCL!$E$2:$R$2
B11	List	=KPIRawDataCL!$F$4:$R$4

 

[KRice]

OK, thanks. Your averaging formula:

AVERAGEIFS(KPIRawDataCL!$M$5:$M$125, KPIselection, B17#)

is only looking at column M in the data table...that's one issue. But let me ask, are the bases for the percentages in the source data equivalent? Generally, averaging percentages is a bad idea unless the basis for each of the items to be averaged is the same. Taking the average of 1/1 (100%) and 500/1000 (50%) produces 75% (using the percentages), but 501/1001 is about 50%. Your achievement scores described in earlier posts count how many times some performance metric (expressed as a percentage) meets a target percentage relative to the total number of events where the comparison can be made. But now it appears that you want to average the performance metrics directly, without any consideration of the target percentages...is that meaningful to you?

 

[Holger]

Thanks. That the formula is only looking at col M is exactly what I can't seem to solve. I tried replacing M5:M125 with the modata we defined earlier but then it somehow does not take the KPIselection into account and throws out a huge array instead of populating the KPI rows....

As the KPI target for a specific KPI and for a client is the same in each state and hence for the entire client averaging the performance for a KPI should be okay without comparing if the tagret is over-/underachieved. The result data is anyways used in graphs for month, qtr, year.

 

[KRice]

The AVERGEIFS function is taking into account the KPI's. I've highlighted in yellow how the formula looks at KPI 4b, then averages the two yellow values from the source data (I've copied the full KPI list, targets, and column M (i.e., month label 8) into columns K:M here for convenience). Next KPI 5a is looked at, only one is found, and it is reported back as the average, etc. The array result obtained is taking into account the aggregation of raw data into groupings determined by the unique KPI in column B.

MrExcel_20220423_Holger.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
16	KPI										Target	Col M, Mo 8
17	4b							0.99855		4b	98.50%	99.71%
18	5a							0.9934		5a	98.00%	99.34%
19	7							1		7	99.00%	100.00%
20	1a							1		1a	99.00%	100.00%
21	2							1		2	98.00%	100.00%
22	3a							1		3a	98.00%	100.00%
23	3b							0.6786		3b	99.85%	67.86%
24	4a							1		4a	99.50%	100.00%
25										4b	98.50%	100.00%
26										1a	99.00%	100.00%
27										2	98.00%	100.00%
28										3a	98.00%	100.00%
Sheet1

Cell Formulas
Range	Formula
B17:B24	B17	=UNIQUE(KPIRawDataCL!B5:B16)
I17:I24	I17	=LET( modata, FILTER(KPIRawDataCL!$M$5:$X$125,KPIRawDataCL!$M$2:$X$2=B5), KPIselection,KPIRawDataCL!$B$5:$B$125, KPIaverage, AVERAGEIFS(KPIRawDataCL!$M$5:$M$125, KPIselection, B17#), KPIaverage)
Dynamic array formulas.

 

[Holger]

I think I'm so far as well. Maybe my English is confusing. In my formula it defines KPIaverage as KPIaverage, AVERAGEIFS(KPIRawDataCL!$M$5:$M$125, KPIselection, B17#).....

How can I make this dynamic so that if the user selects a diff months the results are updated. I tried to replace red section with modata but that does not work....

 

[KRice]

You need to filter your source table by column (to operate on the correct month) and by row (to aggregate like KPI's), so a nested FILTER function is used to define a modified monthly data subset called mokpidata. When you do this, make sure the array sizes from one filter are compatible with the other filter. In this case, the inner FILTER has indexes from 5 to 125, so the outer FILTER also references the KPI range from B5:B125.

MrExcel_20220423_Holger.xlsx
	B	C	D	E	F	G	H	I
4	Select Month Label		Achievement		Count Not Blank	Count Exceeding Target		All in One
5	8		0.9166667		12	11		0.9166667
6
7	Select Quarter		Achievement
8	4		0.9166667		36	33		0.9166667
9
10	Select Mo-Yr for YTD		Achievement
11	44621		0.9166667		36	33		0.9166667
12
13
14	Average KPI Achievement by KPI
15
16	KPI					Avg for month in B5 per KPI
17	4b					0.99855
18	5a					0.9934
19	7					1
20	1a					1
21	2					1
22	3a					1
23	3b					0.6786
24	4a					1
Sheet1

Cell Formulas
Range	Formula
D5	D5	=LET( modata, FILTER(KPIRawDataCL!$M$5:$Y$125,KPIRawDataCL!$M$2:$Y$2=$B5), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((modata<>0)*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>0)), cgtetarget/cbasis )
F5	F5	=LET( modata, FILTER(KPIRawDataCL!$M$5:$Y$125,KPIRawDataCL!$M$2:$Y$2=$B5), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((modata<>0)*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>0)), cbasis )
G5	G5	=LET( modata, FILTER(KPIRawDataCL!$M$5:$Y$125,KPIRawDataCL!$M$2:$Y$2=$B5), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((modata<>0)*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>0)), cgtetarget )
I5	I5	=LET( rawdata, KPIRawDataCL!$M$5:$Y$125, target, KPIRawDataCL!$L$5:$L$125, modata, FILTER(rawdata,KPIRawDataCL!$M$2:$Y$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$M$3:$Y$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$M$4:$Y$4<=$B$11)*(YEAR(KPIRawDataCL!$M$4:$Y$4)=YEAR($B5))), data, modata, cgtetarget, SUMPRODUCT((data<>0)*(data>=target)), cbasis, SUMPRODUCT(--(data<>0)), cgtetarget/cbasis )
D8	D8	=LET( qtrdata, FILTER(KPIRawDataCL!$M$5:$Y$125,KPIRawDataCL!$M$3:$Y$3=$B8), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((qtrdata<>0)*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>0)), cgtetarget/cbasis )
F8	F8	=LET( qtrdata, FILTER(KPIRawDataCL!$M$5:$Y$125,KPIRawDataCL!$M$3:$Y$3=$B8), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((qtrdata<>0)*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>0)), cbasis )
G8	G8	=LET( qtrdata, FILTER(KPIRawDataCL!$M$5:$Y$125,KPIRawDataCL!$M$3:$Y$3=$B8), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((qtrdata<>0)*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>0)), cgtetarget )
I8	I8	=LET( rawdata, KPIRawDataCL!$M$5:$Y$125, target, KPIRawDataCL!$L$5:$L$125, modata, FILTER(rawdata,KPIRawDataCL!$M$2:$Y$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$M$3:$Y$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$M$4:$Y$4<=$B$11)*(YEAR(KPIRawDataCL!$M$4:$Y$4)=YEAR($B8))), data, qtrdata, cgtetarget, SUMPRODUCT((data<>0)*(data>=target)), cbasis, SUMPRODUCT(--(data<>0)), cgtetarget/cbasis )
D11	D11	=LET( yrdata, FILTER(KPIRawDataCL!$M$5:$Y$125,(KPIRawDataCL!$M$4:$Y$4<=$B11)*(YEAR(KPIRawDataCL!$M$4:$Y$4)=YEAR($B11))), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((yrdata<>0)*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>0)), cgtetarget/cbasis )
F11	F11	=LET( yrdata, FILTER(KPIRawDataCL!$M$5:$Y$125,(KPIRawDataCL!$M$4:$Y$4<=$B11)*(YEAR(KPIRawDataCL!$M$4:$Y$4)=YEAR($B11))), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((yrdata<>0)*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>0)), cbasis )
G11	G11	=LET( yrdata, FILTER(KPIRawDataCL!$M$5:$Y$125,(KPIRawDataCL!$M$4:$Y$4<=$B11)*(YEAR(KPIRawDataCL!$M$4:$Y$4)=YEAR($B11))), target, KPIRawDataCL!$L$5:$L$125, cgtetarget, SUMPRODUCT((yrdata<>0)*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>0)), cgtetarget )
I11	I11	=LET( rawdata, KPIRawDataCL!$M$5:$Y$125, target, KPIRawDataCL!$L$5:$L$125, modata, FILTER(rawdata,KPIRawDataCL!$M$2:$Y$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$M$3:$Y$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$M$4:$Y$4<=$B$11)*(YEAR(KPIRawDataCL!$M$4:$Y$4)=YEAR($B11))), data, yrdata, cgtetarget, SUMPRODUCT((data<>0)*(data>=target)), cbasis, SUMPRODUCT(--(data<>0)), cgtetarget/cbasis )
B17:B24	B17	=UNIQUE(KPIRawDataCL!B5:B16)
G17:G24	G17	=LET( mokpidata,FILTER(FILTER(KPIRawDataCL!$M$5:$X$125,(KPIRawDataCL!$M$2:$X$2=$B$5)),KPIRawDataCL!$B$5:$B$125=$B17), AVERAGE(mokpidata) )
Dynamic array formulas.

 

[KRice]

Since there is no redundancy in the AVERAGE formula, I would probably opt to not incorporate it into the LET function. You can if you prefer to see the definition of mokpidata (monthly data parsed by KPI) explicitly. Without the LET function, the formula would be:

=AVERAGE(FILTER(FILTER(KPIRawDataCL!$M$5:$X$125,(KPIRawDataCL!$M$2:$X$2=$B$5)),KPIRawDataCL!$B$5:$B$125=B17))

 

[Holger]

Awesome Kirk. Thank very much again. I got both ways working and will see if I can adapt for Qtr and YTD filtering to get average for those as well.

Again thanks for all your efforts.

 

[KRice]

Glad to help. So that I understand, for quarterly averaging, Qtr 4 and KPI 4B would have 6 values to average in this example?

 

[Holger]

Yes correct and with Q4 and KPI 4b result would be 99.95%.

One last thing I corrected (dare I say ), is in the original all-in-one section the formula for QTR and YTD

LET Function and SUMPRODUCT.xlsx
	B	C	D	E	F	G	H	I
4	Select Month Label		Achievement		Count Not Blank	Count Exceeding Target		All in One
5	8		91.67%		12	11		91.67%
6
7	Select Quarter		Achievement
8	4		91.67%		36	33		91.67%
9
10	Select Mo-Yr for YTD		Achievement
11	Mar-22		94.79%		96	91		94.79%
12
13
14	Dashboard 1
15								Month
16	KPI
17	4b							99.86%
18	5a							99.34%
19	7							100.00%
20	1a							100.00%
21	2							100.00%
22	3a							100.00%
23	3b							67.86%
24	4a							100.00%
Sheet1

Cell Formulas
Range	Formula
D5	D5	=LET( modata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$2:$R$2=$B5), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((modata<>"")*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>"")), cgtetarget/cbasis )
F5	F5	=LET( modata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$2:$R$2=$B5), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((modata<>0)*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>0)), cbasis )
G5	G5	=LET( modata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$2:$R$2=$B5), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((modata<>0)*(modata>=target)), cbasis, SUMPRODUCT(--(modata<>0)), cgtetarget )
I5	I5	=LET( rawdata, KPIRawDataCL!$F$5:$R$125, target, KPIRawDataCL!$E$5:$E$125, modata, FILTER(rawdata,KPIRawDataCL!$F$2:$R$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$F$3:$R$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$F$2:$R$2<=KPIRawDataCL!$Q$2)*(KPIRawDataCL!$F$2:$R$2>=$B$5)), data, modata, cgtetarget, SUMPRODUCT((data<>"")*(data>=target)), cbasis, SUMPRODUCT(--(data<>"")), cgtetarget/cbasis )
D8	D8	=LET( qtrdata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$3:$R$3=$B8), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((qtrdata<>"")*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>"")), cgtetarget/cbasis )
F8	F8	=LET( qtrdata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$3:$R$3=$B8), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((qtrdata<>0)*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>0)), cbasis )
G8	G8	=LET( qtrdata, FILTER(KPIRawDataCL!$F$5:$R$125,KPIRawDataCL!$F$3:$R$3=$B8), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((qtrdata<>0)*(qtrdata>=target)), cbasis, SUMPRODUCT(--(qtrdata<>0)), cgtetarget )
I8	I8	=LET( rawdata, KPIRawDataCL!$F$5:$R$125, target, KPIRawDataCL!$E$5:$E$125, modata, FILTER(rawdata,KPIRawDataCL!$F$2:$R$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$F$3:$R$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$F$2:$R$2<=KPIRawDataCL!$Q$2)*(KPIRawDataCL!$F$2:$R$2>=$B$5)), data, qtrdata, cgtetarget, SUMPRODUCT((data<>"")*(data>=target)), cbasis, SUMPRODUCT(--(data<>"")), cgtetarget/cbasis )
D11	D11	=LET( yrdata, FILTER(KPIRawDataCL!$F$5:$R$125,(KPIRawDataCL!$F$2:$R$2<=KPIRawDataCL!$Q$2)*(KPIRawDataCL!$F$2:$R$2>=Sheet1!B5)), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((yrdata<>"")*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>"")), cgtetarget/cbasis )
F11	F11	=LET( yrdata, FILTER(KPIRawDataCL!$F$5:$R$125,(KPIRawDataCL!$F$2:$R$2<=KPIRawDataCL!$Q$2)*(KPIRawDataCL!$F$2:$R$2>=Sheet1!B5)), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((yrdata<>0)*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>0)), cbasis )
G11	G11	=LET( yrdata, FILTER(KPIRawDataCL!$F$5:$R$125,(KPIRawDataCL!$F$2:$R$2<=KPIRawDataCL!$Q$2)*(KPIRawDataCL!$F$2:$R$2>=Sheet1!B5)), target, KPIRawDataCL!$E$5:$E$125, cgtetarget, SUMPRODUCT((yrdata<>"")*(yrdata>=target)), cbasis, SUMPRODUCT(--(yrdata<>"")), cgtetarget )
I11	I11	=LET( rawdata, KPIRawDataCL!$F$5:$R$125, target, KPIRawDataCL!$E$5:$E$125, modata, FILTER(rawdata,KPIRawDataCL!$F$2:$R$2=$B$5), qtrdata, FILTER(rawdata,KPIRawDataCL!$F$3:$R$3=$B$8), yrdata, FILTER(rawdata,(KPIRawDataCL!$F$2:$R$2<=KPIRawDataCL!$Q$2)*(KPIRawDataCL!$F$2:$R$2>=$B$5)), data, yrdata, cgtetarget, SUMPRODUCT((data<>"")*(data>=target)), cbasis, SUMPRODUCT(--(data<>"")), cgtetarget/cbasis )
B17:B24	B17	=UNIQUE(KPIRawDataCL!B5:B16)
I17:I24	I17	=LET( mokpidata,FILTER(FILTER(KPIRawDataCL!$F$5:$R$125,(KPIRawDataCL!$F$2:$R$2=$B$5)),KPIRawDataCL!$B$5:$B$125=$B17), AVERAGE(mokpidata) )
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B8	List	1,2,3,4
B5	List	=KPIRawDataCL!$E$2:$R$2
B11	List	=KPIRawDataCL!$F$4:$R$4