Results 1 to 5 of 5

Compound countif statement

This is a discussion on Compound countif statement within the Excel Questions forums, part of the Question Forums category; Hello, I need some assistance with a countif, or a better formula to use. I want to count all the ...

  1. #1
    New Member
    Join Date
    Nov 2005
    Posts
    18

    Default Compound countif statement

    Hello, I need some assistance with a countif, or a better formula to use. I want to count all the items in column D that match "Customer" only if the range in column I matches "New England". Any assistance would be appreciated.


    COUNTIF(I2:I201, "*"&"New England"&"*"),(D2:D201, "*"&"Customer"&"*"))

  2. #2
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Compound countif statement

    Quote Originally Posted by mikeconstantino View Post
    Hello, I need some assistance with a countif, or a better formula to use. I want to count all the items in column D that match "Customer" only if the range in column I matches "New England". Any assistance would be appreciated.


    COUNTIF(I2:I201, "*"&"New England"&"*"),(D2:D201, "*"&"Customer"&"*"))
    Try one of these...

    Use cells to hold the criteria:

    A2 = Customer
    B2 = New England

    If you're using Excel 2007 or later:

    =COUNTIFS(D2:D201,"*"&A2&"*",I2:I201,"*"&B2&"*")

    This one will work in all versions of Excel:

    =SUMPRODUCT(--(ISNUMBER(SEARCH(A2,D2:D201))),--(ISNUMBER(SEARCH(B2,I2:I201))))
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  3. #3
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,219

    Default Re: Compound countif statement

    Which version of Excel are you using?
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  4. #4
    New Member
    Join Date
    Nov 2005
    Posts
    18

    Default Re: Compound countif statement

    I am using 2010

  5. #5
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,219

    Default Re: Compound countif statement

    There is not much to be added after Biff's recommedation.
    I would rather make my ranges absolute($$) but unless you will not drag your formulas down or accross T.Valko examples are ideal.
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com