Conditional count blank cells formula

Hi, first time posting - hoping you can help as struggling to find the answer. I have a report which is pulled from a database compiled by a wide range of staff. One of the questions is a simple 'Yes/ No' drop down but is not a required field. For a variety of reasons it is difficult to make it required and I'm loathe to anyway if there is an alternative.

The data is pulled into Excel and for the field in question shows either "Y", "N" or a blank if it wasn't answered. I have a separate table which provides a summary of the responses in that column and would like to group the blanks and N's however I'm struggling to get a countif to pick up the blanks. It basically needs to count blank cells in column B where column A is not blank. I've tried using countblank but that would require the formula being updated regularly to cover only those rows with data (there are only between 500-2000 rows).

Hope this makes sense, any ideas appreciated.

Hi,

you could try =SUMPRODUCT((A2:A10<>"")*(B2:B10=""))

regards
Michael

Thanks all. Would've liked to have used countifs as it's consistent with the other formulae in the spread sheet but it won't pick up the blanks for some reason.

The sumproduct worked a treat though - hadn't used it before - thanks for the help!
Jimbo