+ Reply to Thread
Results 1 to 9 of 9

problem with countifs

  1. #1
    Registered User
    Join Date
    05-26-2011
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    5

    problem with countifs

    I have a column of data that contains a two letter identifier, which can sometimes be combined with another 2 letter indentifier: Example

    Column D
    AA
    DC
    DC,AA

    how would I get countifs to count each occurance of AA or DC by itself?

  2. #2
    Forum Contributor
    Join Date
    11-26-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: problem with countifs

    Would this work for you?

    =COUNT(SEARCH("AA";D1:D3))

    (Arrayed so when you type it, press ctrl+shift+enter instead of enter)
    If you get an error message, try replacing ; with ,

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: problem with countifs

    Hi,

    Can any of the strings you wish to count also form part of a diferent string? For example, are there any strings such as "XXDC1", or any other that may contain "DC" which you would not wish to form part of the count for "DC"?

    If you can't be certain of this, that's fine. It's just that the solution is a little more complex in that case, so I thought I'd ask first, since if you could guarantee that the above is not possible then I could give you a solution right now.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

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

    Re: problem with countifs

    If they are all two letters separated by commas then I assume you can safely use wildcards, e.g. to count "AA" try

    =COUNTIF(D:D,"*AA*")

    or if you want to use a cell reference as criterion, e.g. with AA in B2

    =COUNTIF(D:D,"*"&B2&"*")
    Audere est facere

  5. #5
    Registered User
    Join Date
    05-26-2011
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: problem with countifs

    Opps, for got to add, the AA, or DC is part of a second worksheet in cell F2. my countifs formula looks something like this:

    =countifs(worksheet1($A$3:$A$200,worksheet2(a2),worksheet1($d$3:$d$200),worksheet2(f2)).

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

    Re: problem with countifs

    Using my suggestion then the last criterion would become this:

    "*"&Worksheet2!F2&"*"

  7. #7
    Registered User
    Join Date
    05-26-2011
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: problem with countifs

    Thank you all for the help. Just got handed a complication for this item:

    Worksheet 1
    1 Column D:
    2 AA
    3 DC
    4 DC/AA
    5 DC(Q)

    Worksheet2
    Column F:
    2 DC

    =countifs(worksheet1($A$2:$A$200),worksheet2(a1),worksheet1($d$2:$d$200),worksheet2(f2))

    I need countifs to add up each instance of DC, less the DC(Q) circumstance.

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

    Re: problem with countifs

    Try adding another criteria for "<>DC(Q)" or if that might be amongst other text make it "<>*DC(Q)*"

  9. #9
    Registered User
    Join Date
    05-26-2011
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: problem with countifs

    Thank you, that gave me the answer I needed

+ 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] Problem using Countifs
    By pamc in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-07-2014, 04:32 PM
  2. [SOLVED] Problem with COUNTIFS
    By jrlafrance in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2013, 02:59 PM
  3. Problem with using countifs
    By alb2p in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-14-2013, 01:36 PM
  4. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  5. COUNTIFS problem
    By javajoemorgan in forum Excel General
    Replies: 8
    Last Post: 10-15-2009, 11:28 AM

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