Sumproduct for qtrly data

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

Hi Jan,

sorry about that. I'm obviously not very good at these matters. Please see attached a sample file with the yellow cells showing where I'm stuck. Any help would be awesome.

Rgds

Thank You so much janmorris and kvsrinivasamurthy for your help. Really appreciate it. Have been trying to solve this for days. If I look at the formula it looks straight forward and I guess the challenge for me is, that I need to better understand how arrays really work .

Will transport data to my file and hopefully get it sorted.

Thanks again

Hi Guys,

I think I have figured it out so thought I share the result in the attached file. I added another array in the formula so that for QTR and YTD the range looks for from...to.... .

Have tested it in my data and it looks to work.

Thanks everyone for the help and hope this helps others too.

Cheers for now