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.
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.