Hello,

I am working with a spreadsheet that contains customer survey results. All customers who received the survey are listed and categorized by various filterable criteria. Columns represent responses for each survey question (values between 1 and 5). If a customer did not complete a survey, the values for each response are shown as #N/A.

For example:
Category Customer ANS1 ANS2 ANS3 ANS4
Clothing ABC Corp 3 4 4 5
General DEF Inc 2 4 3 3
Clothing GHI LLC #N/A #N/A #N/A #N/A
Shoe JKL Co 4 4 4 4

What I need is a formula at the bottom of each column to count the number of responses of each possible value, but only for the visible customers when the table is filtered by category. Searching this and other forums I discovered this formula which achieved what I needed at first (in this case, the formula counts the number of "4" responses in the column which holds the results for question 2a):
Formula: copy to clipboard 
=SUMPRODUCT(SUBTOTAL(3,OFFSET(I$5,ROW(I$5:I$145)-ROW(I$5),0)),(I$5:I$145=4)+0)

This formula worked with blank cells representing a non-response, until I realized that other formulas and conditional formats on other sheets in the book were not behaving as they should. I replaced the blank cells with #N/A (like in the example table above), which fixed the other sheets, but broke the formula above.

How can I alter the formula above to have it ignore the #N/A cells (or treat them like blank cells)?

Thank you, and my apologies if this has been covered elsewhere; I could not locate any threads which covered this situation.