Counting blank cells with Conditions

[raisedwell]

I need help with a formula that would allow me to determine the following:

I have a workbook where am using Sheet1 as a dashboard that will help determine how many questions were not answered found on Sheet2.

Scenario:
Sheet 2 has a comprehensive list of questions to which Column A contains the list of 50 questions, Column B should contain 50 responses. If they they provide YES to the first question, (Sheet2, B1) then the rest of the questions are out of scope and should not be counted, otherwise, if the first question is answered NO, then count the number of blank cells between Sheet2, B1 thru B50

I can obtain the number of blank cells by using =COUNTBLANK(Sheet2!A1:A50) however, I cannot figures out how to add a condition of making the remaining questions out of scope if the first question is answered YES.

Thanks in advance for any help provided.

 

[Fluff]

How about
=if(Sheet2!B1="NO", COUNTBLANK(Sheet2!B1:B50),"")

 

[raisedwell]

Thank you. I just realized that the possible responses I have that they can choose is Yes, No, and N/A.

While the formula does return the number of unanswered cells if NO is chosen, however if they say YES it returns a blank cell. (Due that other calculations also reference the cell of unanswered cells, I need to be able for if the answer is YES, then it returns 0 as unanswered questions.

Make sense?

 

[Fluff]

In that case change the "" at the end of the formula to 0

 

[raisedwell]

Works perfectly for No.

YES - Returns 0
NO - Returns # of unanswered cells
N/A - Returns 0 (Need it to also return # of unanswered cells)

Or return # of unanswered cells unless answered YES.

I hope this helps. (I cannot thank you enough!)

 

[Fluff]

In that case try
=if(Sheet2!B1="Yes",0, COUNTBLANK(Sheet2!B1:B50))

 

[raisedwell]

ABSOLUTELY PERFECT!

 

[Fluff]

You're welcome & thanks for the feedback.