count depending on...

[Mike7]

In column M I need to do count of three cells in the range B2K7. The range B1K1 - numbers of columns, where 1 is number for column B, 2 - for column C, etc. The range O2Q2 - numbers of columns I need to count.
I would like to automate it in the following way: when I put numbers of columns in range O2Q2 then in column M matching column letters will be counted.
Also O2Q2 numbers could be in one cell separated by comma.
Thanks.

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		1	2	3	4	5	6	7	8	9	10
2	1		2		4	5	6			9			3		2	5	9
3	2	1		3				7	8		10		0
4	3	1	2	3				7	8				1
5	4				4	5	6			9	10		2
6	5	1				5		7	8		10		1
7	6		2	3	4		6			9			2
8
Sheet1

 

[barry houdini]

Try this formula in M2 copied down

=COUNT(INDEX(B2:K2,O$2),INDEX(B2:K2,P$2),INDEX(B2:K2,Q$2))

 

[Mike7]

Works good.
Big thanks.

 

[sgremmo]

It will be:

SUM(IF(HLOOKUP(O$2,$B:$K,$A2+1,FALSO)<>"",1,),IF(HLOOKUP(P$2,$B:$K,$A2+1,FALSE)<>"",1,),IF(HLOOKUP(Q$2,$B:$K,$A2+1,FALSE)<>"",1,))

 

[sgremmo]

I restarted my computer and meantime is arrived barry houdini.
My compliments for the elegance of your formula.

 

[Mike7]

I could not get it working before I turned Italian "Falso" to English "False"
Big thanks too!