Conditional formatting - Count the number of cells in a row

[louisli_evo]

Hi, I'm modifying an old worksheet to add an ability to check if some data throughout 12 months of a year has been correctly filled. I have verified the condition on separate cells but seems conditional formatting doesn't read the same formula syntax.

Sheet "Figures":

For example, if the frequency is "Annually", then it checks cells F2-Q2 if one of the cells is filled (or if eleven (11) hyphens "-" are left). If the frequency is quarterly, it checks cells F2-Q2 if four of the cells are filled (or if eight (8) hyphens "-" are left). If this condition is true, the background of "Frequency" changes to "no colour".

I applied conditional formatting on this "Frequency" column (D), with the formula:
="COUNTIF(F2:Q2,"-")==VLOOKUP('Figures'!D2,'(Controls)'!A2:C5,3)"

The Frequency on this sheet is just a reference to another cell in the sheet "Figures".
=Metrics!G2

And the Frequency on "Metrics" sheet has the following options (using data validation, from a list on sheet "(Controls)")
- Annually
- Biannually
- Quarterly
- Monthly
So, this is what I put on sheet "(Controls")

Did I miss anything? It seems close but I just couldn't figure out what's the problem.
Thank you very much.

 

[shinigamilight]

=VLOOKUP(D2,Controls!$A$2:$C$5,3,0)=SUMPRODUCT(--ISTEXT(F2:Q2))

 

[Peter_SSs]

If the cells that are filled in are filled with numbers, then COUNT would be a simple function to use to check

louisli_evo.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Annually		-	-	-	-	-	-	6	-	-	-	-	-
3	Biannually		-	-	6	-	-	-	-	-	-	-	-	-
4	Monthly		2	4	3	6	5	4	5	6	7	8	9	7
5	Quarterly		-	-	4	-	-	4	-	-	3	-	-	2
Figures

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D5	Expression	=COUNT(F2:Q2)=VLOOKUP(D2,'(Controls)'!A$2:B$5,2,0)	text	NO

If the non-dash cells are, or could be, text then tidying up your attempted CF formula above would be ..
- You had two = signs together in the middle of your formula
- Your formula was enclosed with double-quote marks
- Your range in the Controls sheet needs $ signs to anchor the rows A$2:C$5
- the VOLOOKUP function is missing the 4th argument of ",0"
- It is not a good idea to use the sheet name of the sheet the formula is used in within the formula (eg 'Figures')

louisli_evo.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Annually		-	-	-	-	-	-	x	-	-	-	-	-
3	Biannually		-	-	y	-	-	-	-	-	-	-	-	-
4	Monthly		2	4	3	6	5	d	5	6	7	8	9	7
5	Quarterly		-	-	y	-	-	x	-	-	w	-	-	y
Figures (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D5	Expression	=COUNTIF(F2:Q2,"-")=VLOOKUP(D2,'(Controls)'!A$2:C$5,3,0)	text	NO