Countif dynamic column based on target column

[ncvnrjn]

Hi there,
I have a planning file with week numbers in row 1, dates in row 2 and target values in column B (similar to the attached picture).

In a seperate sheet I'm trying to build a KPI that is: the percentage of planned amounts that are >= target amounts, per _week_, ignoring blanks.

Output should be something like:
Week 24: 57%
Week 25: 77%

I've been struggling to specify a dynamic range of 5 colums (all week 24, for example) and then do the countif or sumproduct.
Hope someone understands and can help.

 

[Fluff]

In future it would be helpful if you could post some data rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				24	24	24	24	24	25	25	25	25	25
2
3	123	4		6					2		2				24	54%
4	124	4		5					6						25	69%
5	125	5								7
6	126	5				4				7
7	127	8		2					11
8	128	2			3
9	129	6			3							6
10	130	6			3							6
11	131	6				4					4
12	132	3				4					4
13	133	4					5						3
14	134	4					8
15	135	2						3					2
16	136	7						6					7
Home

Cell Formulas
Range	Formula
P3:P4	P3	=SUMPRODUCT(($D$3:$M$16>=$B$3:$B$16)*($D$3:$M$16<>"")*($D$1:$M$1=O3))/SUMPRODUCT(($D$1:$M$1=O3)*($D$3:$M$16<>0))

 

[ncvnrjn]

That's just remarkable!
Functions exactly the way I want it too. Thanks a million.

I'll bear in mind the XL2BB Add-in for future posts, thank you.

 

[Fluff]

You're welcome & thanks for the feedback.