Formula that counts the number of times a certain set of conditions are met

[lar9149]

Hello again all

I am trying to create a formula that counts the number of times (in the specified example) in a range the below set of conditions are met

1. When the specified number appears in column C in a string of text (in the example, the numbers are 5,6,7)
2. In the same row of the specified number, a 0 appears column BC, BD, BE, or there is a number in column BI

In the example, I try to create my own formula, but it appears not be working. The correct numbers are in red row 13-15 column F. I highlighted the numbers 5,6,7 in blue, green, and red respectively

The harder part is row 11 column c that says 1u/5d. I would like to exclude the specified number like 5 when it is next to / like this.

Let me know if any farther clarification is required and thank you.

[Glenn Kennedy]

There is a mass of clutter in your file. Please simplify it to show the raw data and your (manually calculated) expected answers and (preferably) nothing else. As it stands... I do not follow it at all!!

[JohnTopley]

What about "a5/a6" vs "1u/5d": is "5/" valid vs "/5" invalid ?

Try

in C13

=SUM(IF((--ISNUMBER(SEARCH($B13,$C$5:$C$12))*(--(NOT(ISNUMBER(SEARCH("/" &$B13,$C$5:$C$12)))))*(($BD$5:$BD$12=0)+($BE$5:$BE$12=0)+($BF$5:$BF$12=0))*(--ISNUMBER($BI$5:$BI$12))),1,0))

Enter with Ctrl+Shift+Enter