COUNTIF with a twist

[brunette]

Hi there,

I'm using Excel 2003 and I need to count cells in column B based on 2 things. 1) if the cells in column B contain the letters MBX. and 2) if the cells in column A contain the letter a.

Let me try to exemplify:

ColumnA ColumnB
a mbxcpprocsb
a mbxcooart
b mbxcpofrt
a pdlagoper

The result I'm looking for is 2, since only 2 cells meet the above criteria. I just can't get it right to write a working formula for that.

Your help is appreciated, as always. Thanks in advance

ps. I would post up a screenshot showing better what I'm after but I don't have admin rights on my work pc and can't install the program necessary to do this. :s

 

[JamesW]

Hi,

Is mbx always the first 3 characters?

If so try:

=SUMPRODUCT(--(A1:A4="a")*(LEFT(B1:B4,3)="mbx"))

 

[brunette]

Hi there,

MBX is always the prefix to the rest of the letters, yes. the way you'll see it in a cell is always MBX-xxxxxxx.

Also, I tried the formula and it errored. It wanted an extra parenthesis at the end and then when I did it, it gave a #value! error.

The formula I used is: =SUMPRODUCT(--(A1:A1000="a")*(LEFT(B1:B1000;3="mbx")))

 

[JamesW]

Hi,

Did you copy my formula exactly?

The ="mbx" needs to be outside of the LEFT.

 

[Dryver14]

Brunette,

You have used a ; where James has placed a , after B1000

 

[brunette]

Oh DUH!

I'm an idiot lol. Fixed and working perfectly. You're a star. Thanks much!

 

[brunette]

Brunette,

You have used a ; where James has placed a , after B1000

That's intended. My regional settings are finicky that way

Fixed my botch up with the parenthesis now, though. Life's good again :D

 

[brunette]

Ugh, I'm sorry guys, I need one last bit of help, then my report is done and I can finally stop bugging you.

A few sheets down the line, I have exactly the same thing as above, except I can't use the LEFT formula anymore, because I need to count all the cells that contain "LI" (which is the only common part of the path I'm looking for) in cells that contain info that looks like this:
ColumnB
D_PLI_LI222G125
D_PLI_OFI132

How would I weed out the cells that contain "LI" in them, whilst still maintaining the rest of the criteria in the formula?

I'm also going to need to count the cases where "OFI" is the criteria I'm looking for, in a spearate instance, so bear in mind I'm going to need to tweak the formula to see that info by itself also.

Thanks again for your patience with me.

 

[JamesW]

Hi brunette,

As per your PM:

=SUMPRODUCT(--(A1:A1000="a")*(ISNUMBER(FIND("LI",B1:B1000))))

Using the above it doesn't matter where the text is. If you want to find OFI later, then change "LI" to "OFI".

 

[brunette]

Awesome. Thanks millions. I'm a very happy puppy right now. (boss will be chuffed, too!)

I'll get out of your hair now, too :P