+ Reply to Thread
Results 1 to 4 of 4

Need a COUNTIF-like function to count rows, not cells

  1. #1
    Registered User
    Join Date
    12-05-2014
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    2

    Need a COUNTIF-like function to count rows, not cells

    I'm looking for a formula that will return to me the number of rows in which a particular set of text appears in at least one column. For example:
    Example Data - Colors.PNG

    Each row represents an article, each of which has three sections. Each section includes one or more colors. I want to know how many articles include the color green in at least one section (i.e. in how many rows does the text "green" appear at least once).

    This part was easy: =COUNTIF(B2:D7,"*Green*")
    But of course it returns the number of cells, not the number of rows.
    The color green appears in article 1, 2, 4, and 6, so the answer I'm looking for is 4.

    I'm sure there's an easy way to do it - any help is very much appreciated!
    Thanks in advance.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Need a COUNTIF-like function to count rows, not cells

    Hi, welcome to the forum

    Off the top of my head, I would suggest using a helper (which you can hide), and doing something like this...
    =IF(COUNTIF(B2:D2,"*Green*")>0,1,0)

    Then adding up the helper
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-05-2014
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    2

    Re: Need a COUNTIF-like function to count rows, not cells

    Thanks so much! That will certainly work. Much appreciated.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Need a COUNTIF-like function to count rows, not cells

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

+ 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. Replies: 2
    Last Post: 01-05-2012, 08:36 AM
  2. Count If-How can I use the COUNTIF function to count
    By calli in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-28-2008, 05:34 PM
  3. [SOLVED] Function to display a count of visible cells/rows... doesn't
    By Peter Rooney in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-16-2006, 05:30 AM
  4. Newbe Help with count/countif function
    By Cybertech in forum Excel Formulas & Functions
    Replies: 89
    Last Post: 09-06-2005, 09:05 PM
  5. [SOLVED] In COUNTIF function, how do I count the number of cells which con.
    By CiCi Bird in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-12-2005, 05:06 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