+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    03-23-2012
    Location
    Minneapolis, MN, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    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: copy to clipboard
    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.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

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

    Try this regular formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    03-23-2012
    Location
    Minneapolis, MN, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

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

    Quote Originally Posted by Ron Coderre View Post
    Does that help?
    It certainly does. Thank you. I am curious, though: what is the purpose of putting 1 over the subtotal result?

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

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

    Here's the reason...
    The inner part of the formula returns:
    - Positive Numbers (for matched numbers)
    - Zeros (for unmatched numbers)
    - Errors (for text)

    A sample array might look like this: {1,1,0,0,0,#VALUE!,1}
    Dividing into 1 results in this:
    =1/{1,1,0,0,0,#VALUE!,1}
    ={1,1,#DIV/0!,#DIV/0!,#DIV/0!,#VALUE!,1}
    So, if we have the count of the numeric values then we have the count of matched items.

    Does that help?

  5. #5
    Registered User
    Join Date
    03-23-2012
    Location
    Minneapolis, MN, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count filtered cells using SUBTOTAL
    By Russell Dawson in forum Excel Tips
    Replies: 2
    Last Post: 07-15-2012, 03:39 PM
  2. Sumproduct subtotal filter count only bold cells
    By RxMiller in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-19-2011, 05:28 PM
  3. Count in filtered list and display results in table on right of data
    By raydaw in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2009, 05:14 AM
  4. Replies: 27
    Last Post: 09-06-2005, 04:05 AM
  5. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1