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.....

 

[KRice]

Since these averages by KPI do not involve the target levels, you could still make use of the earlier LET formula and definitions of modata, qtrdata, yrdata, except each of those needs to be filtered again to isolate only the rows pertaining to a specific KPI. In this example, I've fed the earlier mo/qtr/yr data into the FILTER function for KPIs and called the resulting subset of data kpimodata, kpiqtrdata, and kpiyrdata. Any of those data subsets can then be assigned to "data" and we take AVERAGE(data) for the final answer. Will there ever be blanks in the source data table? If so, some adjustment may be needed to the formulas. As I mentioned before, you could trim these formulas down by stripping away the LET function and using only the relevant portions for the intended computation. Whether you do that depends on your preference for keeping the definitions together.

MrExcel_20220423_Holger.xlsx
	B	C	D	E	F	G	H	I
14	Average KPI Achievement by KPI for…
15
16	KPI				month in B5	month in B5	quarter in B8	YTD wrt Mo-Yr in B11
17	4b				99.86%	99.86%	99.95%	99.95%
18	5a				99.34%	99.34%	99.08%	99.08%
19	7				100.00%	100.00%	100.00%	100.00%
20	1a				100.00%	100.00%	100.00%	100.00%
21	2				100.00%	100.00%	100.00%	100.00%
22	3a				100.00%	100.00%	100.00%	100.00%
23	3b				67.86%	67.86%	70.44%	70.44%
24	4a				100.00%	100.00%	100.00%	100.00%
Sheet1

Cell Formulas
Range	Formula
B17:B24	B17	=UNIQUE(KPIRawDataCL!B5:B16)
F17:F24	F17	=AVERAGE(FILTER(FILTER(KPIRawDataCL!$M$5:$X$125,(KPIRawDataCL!$M$2:$X$2=$B$5)),KPIRawDataCL!$B$5:$B$125=B17))
G17:G24	G17	=LET( rawdata, KPIRawDataCL!$M$5:$Y$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($B$11))), kpimodata,FILTER(modata,KPIRawDataCL!$B$5:$B$125=$B17), kpiqtrdata,FILTER(qtrdata,KPIRawDataCL!$B$5:$B$125=$B17), kpiyrdata,FILTER(yrdata,KPIRawDataCL!$B$5:$B$125=$B17), data, kpimodata, AVERAGE(data) )
H17:H24	H17	=LET( rawdata, KPIRawDataCL!$M$5:$Y$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($B$11))), kpimodata,FILTER(modata,KPIRawDataCL!$B$5:$B$125=$B17), kpiqtrdata,FILTER(qtrdata,KPIRawDataCL!$B$5:$B$125=$B17), kpiyrdata,FILTER(yrdata,KPIRawDataCL!$B$5:$B$125=$B17), data, kpiqtrdata, AVERAGE(data) )
I17:I24	I17	=LET( rawdata, KPIRawDataCL!$M$5:$Y$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($B$11))), kpimodata,FILTER(modata,KPIRawDataCL!$B$5:$B$125=$B17), kpiqtrdata,FILTER(qtrdata,KPIRawDataCL!$B$5:$B$125=$B17), kpiyrdata,FILTER(yrdata,KPIRawDataCL!$B$5:$B$125=$B17), data, kpiyrdata, AVERAGE(data) )
Dynamic array formulas.