Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Stuck on CountIF problem

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Lets see if I can explain this. My data is as follows:
    Column A | Column B
    Type 1 XYZ1234
    Type 1 ABC2345
    Type 1
    Type 2 XYZ8974
    Type 2 ABC4433
    etc

    I need a count of Type 1's where its corresponding cell in column B starts with XYZ (I don't know why some of them have the <> around them)

    Anyways, I have figured out how to get a count of how many in column B start with XYZ, with the following:
    In C1 I enter "XYZ"
    In D1 I enter =IF(LEN(C1),COUNTIF(B1:B5000,"*"&C1&"*"),"")

    Based on previous posts, I tried a SUMPRODUCT, as follows:

    =SUMPRODUCT((A1:A5000="Type 1")*(IF(LEN(C1),COUNTIF(B1:B5000,"*"&C1&"*"),"")))

    But that just multiplies the two counts together, instead of giving a count of how many times a Type 1 is an XYZ. I tried examples given in previous posts, and they worked, but they were not trying to match on part of a cell as I am here.

    Any ideas? Thanks.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following:

    =SUMPRODUCT((A1:A5000="Type 1")*(LEFT(B1:B5000,3)="XYZ"))

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-09 14:58, DavidC wrote:
    Lets see if I can explain this. My data is as follows:
    Column A | Column B
    Type 1 XYZ1234
    Type 1 ABC2345
    Type 1
    Type 2 XYZ8974
    Type 2 ABC4433
    etc

    I need a count of Type 1's where its corresponding cell in column B starts with XYZ (I don't know why some of them have the <> around them)

    Anyways, I have figured out how to get a count of how many in column B start with XYZ, with the following:
    In C1 I enter "XYZ"
    In D1 I enter =IF(LEN(C1),COUNTIF(B1:B5000,"*"&C1&"*"),"")

    Based on previous posts, I tried a SUMPRODUCT, as follows:

    =SUMPRODUCT((A1:A5000="Type 1")*(IF(LEN(C1),COUNTIF(B1:B5000,"*"&C1&"*"),"")))

    But that just multiplies the two counts together, instead of giving a count of how many times a Type 1 is an XYZ. I tried examples given in previous posts, and they worked, but they were not trying to match on part of a cell as I am here.

    Any ideas? Thanks.
    Try:

    =SUMPRODUCT((A1:A5000=C1)*(ISNUMBER(SEARCH(D1,B1:B5000))))

    where C1 houses "Type 1" and D1 "XYZ".

    Is your data range really that big?

    Aladin

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Those both worked. Thank you much. This board is the greatest.
    Usually my data range is about 2000 rows, but can get bigger. Thanks again.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-09 15:12, DavidC wrote:
    Those both worked. Thank you much. This board is the greatest.
    Usually my data range is about 2000 rows, but can get bigger. Thanks again.
    You could use a dynamically compute range instead of a huge definite range in order to cope with changes to your data area. This would make the SUMPRODUCT formula less expensive.

    BTW, using LEFT(B1:B5000,3)="XYZ" will only consider strings that begins with XYZ, while the SEARCH version will pick out XYZ anywhere within the target string. So the formulas are not the same; adopt the one that meets your question.

    Aladin


  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need a count of Type 1's where its corresponding cell in column B starts with XYZ
    Aladin,
    Both of our codes will work because he stated that the cells in Column B will start with XYZ .

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-09 15:28, Al Chara wrote:
    I need a count of Type 1's where its corresponding cell in column B starts with XYZ
    Aladin,
    Both of our codes will work because he stated that the cells in Column B will start with XYZ .
    Al,

    I wasn't disputing the use of LEFT as such. Seeing his condition in COUNTIF, I thought I better point out the difference. SEARCH will pick out more than LEFT will: e.g., XYZ... vs
    Aladin

Some videos you may like

User Tag List

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
  •