+ Reply to Thread
Results 1 to 4 of 4

Using SUMPRODUCT across columns when I have blanks

  1. #1
    Registered User
    Join Date
    05-02-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    2

    Using SUMPRODUCT across columns when I have blanks

    Hi all - I just learned how to use SUMPRODUCT to count the presence of a value across multiple columns, which is useful. However, I've another one that I don't know how to do, and I'm not sure if I need SUMPRODUCT, COUNTIF or some other voodoo.

    I have data from a survey I did for work. Respondents checked a box for YES if certain criteria applied to them. Those 4 criteria are each in a column across the top, and a Y appears in the corresponding column if they checked the box. If they didn't check the box, it's blank.

    What I want to do is count the instances of someone only checking ONE box out of the 4 - regardless of which one it is.

    So:

    Name Yellow Red Green Blue
    Joe Y Y
    Ed Y
    Steve Y Y
    Jane Y Y Y

    I want a formula to tell me that 1 person (Ed) has answered Y in only one instance, and disregard the others because they have more than one yes.

    Thanks!

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Using SUMPRODUCT across columns when I have blanks

    The COUNTA function should do that for you.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Using SUMPRODUCT across columns when I have blanks

    You can use SUMPRODUCT like this

    =SUMPRODUCT((B2:B10&C2:C10&D2:D10&E2:E10="Y")+0)
    Audere est facere

  4. #4
    Registered User
    Join Date
    05-02-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Using SUMPRODUCT across columns when I have blanks

    Ah, thank you, @daddylonglegs!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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