How to search word in another sheet and get the count of the rows with that word?

[srini0410]

I have a sheet with unique numbers(BUN(business unit number) in this case). I want to search with a word or words in another sheet (Sheet2) where same BUN numbers appear in multiple rows and type of work done in the beside coulumns. in the Sheet 1 I want to put the number of rows( where those particular words appear ) in a column.

For example I want to search word 'maintenance' in the 'job description' in sheet 2 for a particual 'BUN' which may have multiple rows and return the number of those rows where this word is found and put it in the cell beside that 'BUN' in Sheet 1.

Is this possible? How can I accomplish this task as 'Job description' column in Sheet 2 has sentences in it.

Sorry If i confused you but you will understand it when you look at the attached file.

[Ron Coderre]

Using your posted workbook...
This formula returns the count of Sheet2, Col_B rows that contain either "maintenance" or "PM"

Please Login or Register  to view this content.

and this formula returns the count for rows containing either "fault" or "emergency repair"

Please Login or Register  to view this content.

The asterisks (*) in those formulas are wildcards that represent zero or more characters.

edited to include these alternatives:
If some cells may contain both values, but you only want them counted once...
This array formula, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) handles that

Please Login or Register  to view this content.

Also, some cells contain "red herrings"...they contain matching text that is not valid.
Example:
"Equipment" contains "pm"

To minimize formula complexity, you might want to consider a VBA approach.

Is that something you can work with?

[sktneer]

@Ron

I think you missed the BUN criteria in the formula.

Would it be like this then?

On Sheet1
In B2

Please Login or Register  to view this content.

[Ron Coderre]

I DID sktneer...Thanks for catching that.

I also noticed in the data that some cells contain values that match the criteria but are not what we want to count.
Example:
"equipment" contains "pm"
I edited my post to suggest a VBA approach (which could use Regular Expressions) to narrow the criteria for matching cells.

[ThirdFret]

Ron,

Is it possible to use a similar formula as an AND statement instead of an OR? (count the number of cells that contains "fault" AND "maintenance" in the same line?)


Never mind, I figured it out! Thanks for the solution.

Please Login or Register  to view this content.

[Ron Coderre]

Actually, since there can be some double-counting, you might want to try This ARRAY FORMULAS, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
...which include the test for matching BUN and avoids the double counting.

Please Login or Register  to view this content.

Does that help?