 Thanks:  0 Likes:  0

# Thread: Stuck on CountIF problem

1. ## 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.  Reply With Quote

2. ## Try the following:

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

3. ## 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?  Reply With Quote

4. ## 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.  Reply With Quote

5. ## 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.  Reply With Quote

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

7. ## 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
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  Reply With Quote

## User Tag List

#### Posting Permissions

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