Stuck on CountIF problem

DavidC

New Member
Joined
Mar 17, 2002
Messages
26
Lets see if I can explain this. My data is as follows:
Column A | Column B
Type 1 XYZ1234
Type 1 ABC2345
Type 1 <XYZ9876>
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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 <XYZ9876>
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 .
 
Upvote 0
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 <XYZ... LEFT will ignore the latter. If that's desired, the formula with SEARCH would not meet the specs.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top