Search a cell for "Company Name", and count if the adjacent cell says "open"

[jjames1000000]

Hello world,

I work for a construction company, and I've got an excel file for tracking all outstanding items from our various workshops.

In a row, I have a brief description of the item in the first column, then the companies responsible for providing an answer in the second column, then "open" or "closed" in the third column. I want to count how many "open" items each company has at any one time. The challenge is that the cell containing the company names can contain many company names, not just one.

To count the number of times a company is mentioned, I used a COUNTIF command which searched for the company name in the cell, but I then need to add the option that it only counts it if the cell next to it says "open". I tried using SUMPRODUCT command, but that seems to only work if there's only one company in the cell - I can't seem to get it to work when it's looking for one company name in a cell that contains many.

Can anyone please help me with this? I've attached the example file, and any help would be much appreciated!

Thank you!

[Keebellah]

Hi, welcome to the forum
I don't know it it works but you could try the CountIfs again and the company name surrounded by a *

Please Login or Register  to view this content.

Hope it makes sense, just typing it directly no Excel at hand now

[mehmetcik]

Formula for F2 =SUMPRODUCT(--NOT(ISERROR(SEARCH("*"&C12&"*",$C$2:$C$9)))*(--($D$2:$D$9="Open")))

or:

=SUMPRODUCT(--NOT(ISERROR(SEARCH("*"&$C12&"*Open",$C$2:$C$9&$D$2:$D$9))))

[jjames1000000]

You, my friend, are a God amongst men.

Thank you for helping me!