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.

2. Try the following:

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

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?

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.

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.

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 .

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

## 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
•