+ Reply to Thread
Results 1 to 6 of 6

COUNTIFS for multiple text criteria

  1. #1
    Registered User
    Join Date
    05-04-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007, 2010
    Posts
    26

    COUNTIFS for multiple text criteria

    Formula stumped again!

    I'd like to count cells only if they contain certain words in no specific order.
    dha, gluco,GSE and multi
    These cells are not sequential going down because the cells in between should be counted as long as they have dha, gluco and GSE in them

    The formula I came up with seems really cumbersome
    =COUNTIFS(J4,J6,J8,J10,J12,J14,J16, "dha",J4,J6,J8,J10,J12,J14,J16,"multi",J4,J6,J8,J10,J12,J14,J16,"gluco",J4,J6,J8,J10,J12,J14,J16,"gse") ... etc.

    I'd like some help writing the correct and perhaps a more parsimonious formula.

    Thanks as always
    Attached Files Attached Files
    Last edited by lallo.jr; 05-21-2010 at 12:25 PM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: COUNTIFS for multiple text criteria

    You can't list cells like that for the range criteria in COUNTIFS......but you can use OFFSET to generate those cells, i.e.

    =SUM(COUNTIF(OFFSET(J4,{0,2,4,6,8,10,12},0),{"dha";"gluco";"GSE";"multi"}))

  3. #3
    Registered User
    Join Date
    05-04-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007, 2010
    Posts
    26

    Re: COUNTIFS for multiple text criteria

    Could you explain to me how that array works? After inserting the formula it still did not detect that text in the cell to return a "1".

    I have attached the file; sheet 2 is the one I'm working on.

    I also wanted to add to the criteria so that for the day to be counted, both am and pm criteria must be met. (AM must have dha, gluco, GSE, and multi; PM must have gluco, GSE, and dha)

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: COUNTIFS for multiple text criteria

    It's not clear if you the terms of interest could be embedded within other terms or not... if not (ie mutually exclusive) then:

    Please Login or Register  to view this content.
    Note: that AM in Col A can be used to differentiate between rows 4 & 5, 6 & 7 etc...

    If not mutually exclusive then you will need to revert to SUMPRODUCT / Array so as to append the main strings such that you have a common delimiter encompassing all terms within any given string (searching for delimitertermdelimiter)
    Last edited by DonkeyOte; 05-21-2010 at 09:28 AM. Reason: missing "

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: COUNTIFS for multiple text criteria

    Sorry,

    The formula I suggested was just based on your formula, without seeing your attachment I didn't get the full picture. Looks like DonkeyOte has it covered......

  6. #6
    Registered User
    Join Date
    05-04-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007, 2010
    Posts
    26

    Re: COUNTIFS for multiple text criteria

    Quote Originally Posted by DonkeyOte View Post
    It's not clear if you the terms of interest could be embedded within other terms or not... if not (ie mutually exclusive) then:

    Please Login or Register  to view this content.
    Note: that AM in Col A can be used to differentiate between rows 4 & 5, 6 & 7 etc...

    If not mutually exclusive then you will need to revert to SUMPRODUCT / Array so as to append the main strings such that you have a common delimiter encompassing all terms within any given string (searching for delimitertermdelimiter)
    Using the same formula as above I made a cell for AM and one for PM and then added them into a third. That works for me.

+ 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