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)
1650592823921.png
Thank You so much for any help.....
Bookmarks