Stuck on CountIF problem

[DavidC]

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.

[Al Chara]

Try the following:

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

[Aladin Akyurek]

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

[DavidC]

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.

[Aladin Akyurek]

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

[Al Chara]

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 .

[Aladin Akyurek]

On 2002-04-09 15:28, Al Chara wrote:

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