IF/AND Criteria is Blank, Give me "Blank"

[tbesh]

I have a workbook where people regularly populate if they have done a task for that week or not. I'm building a "compliance" sheet that will capture the overall % of completed tasks.

The trouble is getting the blank cells to produce the word "Blank". I DO NOT want the number zero produced for blank cells, as this will throw the conditional formatting off.

For example:

{=IF(AND($G$2:$G$93="*Annually*",$M$2:$M$93=""),"Blank",[COUNTIFS funtion])}

Where the frequency is "Annually" AND the completion status is "" (blank), give me "BLANK", otherwise give me the results of this COUNTIF function:

=(COUNTIFS($G$2:$G$93,"*Annually*",$M$2:$M$93,"*YES*")/COUNTIFS($G$2:$G$93,"*Annually*",$M$2:$M$93,"<>N/A"))

The result I get from the IF/AND funtion is "FALSE" (even if the combination is true).

 

[MARZIOTULLIO]

Hi

Can you provide some sample data and what the expected outcome is?

 

[tbesh]

Ok (I tried to paste or insert from excel with no luck):

Frequency: Completed:
Annually NO

If "Completed" is left blank, give "Blank".

On a separate sheet there's conditional formating for complaice (RED if less than 69%). If there is a value of zero, then the "Blank" celss will produce RED in the conditionally formatted cells.

I hope that helps!