+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS Equivalent Where One Range is Multiple Columns

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    COUNTIFS Equivalent Where One Range is Multiple Columns

    Hi, I am trying to set up a crosstab table showing responses to survey questions. Some of the survey questions allow multiple responses ("Select all that apply"), so there can be a variable number of columns for a single question. See attached example. In this example, each row is a separate survey response, and columns C-F show the (possibly multiple) responses for a single question.

    I need to count (for example) how many selected surveys (column A=1) with female respondents (column B = "F") chose response 2 as one of their responses (anywhere in columns C thru F). So this is kind of like a COUNTIFS, except the last range needs to span multiple (in this case, 4) columns. Number of choices can vary so that last range could be one or more columns for different questions. I need a general solution that works for any specified number of "answer" columns so I can't just string together COUNTIFS (for column C) + COUNTIFS (for column D) + COUNTIFS (for column E) etc.

    COUNTIFS doesn't work with the multiple-column range, and I've tried a variety of array formulas, SUMPRODUCT, logicals, etc. with no luck. Any suggestions?
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: COUNTIFS Equivalent Where One Range is Multiple Columns

    have you tried this... =SUMPRODUCT(($A$2:$A$20=1)*($B$2:$B$20="F")*(C2:F20=2))
    it returns 4 for me from your sample.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: COUNTIFS Equivalent Where One Range is Multiple Columns

    Thanks, Sam! I had tried using SUMPRODUCT, but with commas between the terms rather than asterisks.
    I also tried your same formula but using SUM instead of SUMPRODUCT (and entering it as an array formula), and that works also.

    Can you by any chance explain why SUMPRODUCT works (in this case) with asterisks but not commas?
    And which would be preferable: SUMPRODUCT regular formula or SUM as an array formula? Is there any reason to use one rather than the other?

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: COUNTIFS Equivalent Where One Range is Multiple Columns

    in column L add or remove the values of the ANS you want to count by gender.Example if you write ANS1 in L2 and ANS2 in L3 you will have the count

    relative to F. If you want the count by *** M type M in the cell

    M2.

    If you delete one or more criteria present in column L you will have the relative

    count by ***.







    I2=SUMPRODUCT((ISNUMBER(MATCH(C1:F1,L2:L6,0)))*(B2:B100=M2)*(C2:F100<>""))
    Attached Files Attached Files

+ 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. [SOLVED] SUMIFS Equivalent for Excel 2003 with multiple criteria and range arguments
    By DEER30 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-27-2014, 01:19 AM
  2. Replies: 2
    Last Post: 03-19-2014, 11:47 AM
  3. Google Docs - Help with equivalent to COUNTIFS
    By livifivil in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 11
    Last Post: 05-30-2013, 12:57 PM
  4. [SOLVED] COUNTIFS using (TODAY()-5days) Filter or equivalent
    By Stymple Tweed in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2013, 08:34 AM
  5. Countifs, from multiple columns, within range of years
    By xenomorph8472 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2012, 01:15 PM
  6. Replies: 1
    Last Post: 12-15-2010, 07:28 PM
  7. Equivalent of countifs function for Excel 2003?
    By oneyejack in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-29-2007, 12:51 PM

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