Using SUMPRODUCT and SUBTOTAL to count filtered results containing #N/A values

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:

Please Login or Register  to view this content.

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.

Originally Posted by Ron Coderre

Does that help?

It certainly does. Thank you. I am curious, though: what is the purpose of putting 1 over the subtotal result?

Yes, I understand it now. Thanks again for your assistance!