Referring to text within a cell as part of a COUNTIF formula?

[BritishBuddha]

I want to use a COUNTIF formula to count how many times cells with a range contain specific words. Each cell will contain four words but I only want to count 2 of them. For example:

Cell 6B contains the term BAN10 (one of the words that I want to count),
Cell E2 contains the term EB (yet another word I want to count)

I'm curently using the formula
=COUNTIF(Sheet1!B3:IV151,"BAN10 EB * *")

My data set is pretty big so I'd have to sit and write/edit formulas for hours.

Any help would be great.

 

[barry houdini]

Hello BritishBuddha, welcome to MrExcel, you could try this formula

=SUM(COUNTIF(Sheet1!B3:IV151,"*"&{"BAN10","EB"}&"*"))

That would give you a combined count of cells that contain either "BAN10" or "EB".....although it will double count if a cell contains both of those terms, is that a problem?

 

[Kaps_mr2]

welcome to the board. Try :-

=SUMPRODUCT(--ISNUMBER(FIND("BAN",C4:C8)))

Alter your range to suit. Thanks

Regards

Kaps

 

[BritishBuddha]

Thanks guys, but I don't think I made my problem very clear. Sorry.

Sheet1 contains vehicle information regarding faults, their locations, and two separate states that the vehicle is in at the time. So each cell contains something that looks like this:

ALS1 DBF SIS MASTER

(where als1 is the location and dbf is the fault)

I'm trying to create a sheet that counts cells that have specific locations AND faults and disregard all other information.

Thanks again.

 

[barry houdini]

So does your initial formula work, it looks OK to me.....

...or are you trying to get a simpler way to count for multiple combinations?

 

[BritishBuddha]

Yes, Barry, that's the case, there are about 100 location and 7 faults. It'll take me forever.

 

[BritishBuddha]

Seems that nobody knows. I would have thought that it would have been possible, thank Christ for Find and Replace. Thanks all for your help.

 

[barry houdini]

Sorry BB, I missed your reply.....

Perhaps you have sorted this now but if not....or if you need an easier way.....

Try listing the locations in A2:A100 and the faults in B1:H1 (in a new sheet) and then use this version of your formula in B2

=COUNTIF(Sheet1!$B$3:$IV$151,$A2&" "&B$1&" "&"* *")

then copy across and down to H100

 

[meiirmeiir21]

Hello Every body,

I have a similar request like this but a little bit different..my sheet is:

Column A Column B
N1_N2 N2_N1
N1_N3 N3_N1
N1_N4 N4_N1
. .
. .
. .

I need to count at column C the occurring of column A and B Text in a certain range..
I used =SUM(COUNTIF(Sheet1!B3:IV151,"*"&{"BAN10","EB"}&"*"))
but i still need BAN10 & EB to be the text at the same row A,B then i can drag..
Any suggestions?

 

[meiirmeiir21]

It is Ok , resolved @
http://www.mrexcel.com/forum/showthread.php?t=567832