Hi
I want to be able to scan row 2 to count/find all the 7OE cell entries (A2 and I2) and count them if there is a Y next to it. In the example below it would return 1 as the same 7OE text found in I2 has a k in J2 and would not be counted. I want to be able to use a wildcard as I would extend this for other subjects, e.g., looking at C2 I would want to use *ES1* in the formula as I will be using a separate reference list which does not match the codes below which include staff, room and subject.
I have tried =COUNTIFS($J$5:$GG$5,7OE*, $J$5:$GG$5,"y") and various variations of it where J5:GG5 is the range I want to scan in my larger file but with no luck, either getting a #VALUE error or 0.
Any ideas? Thank you for taking a look at it and any help provided. I have attached the table below to this thread as an Excel file.
Jeff
A | B | C | D | E | F | G | H | I | J | |
1 | ThuA:1 | Present? Y or N | ThuA:2A | Present? Y or N | ThuA:3A | Present? Y or N | ThuA:4A | Present? Y or N | FriA:1 | Present? Y or N |
2 | 70E KM F6 - English | y | 7n/Es1 TD F6 - English | y | 7N1/En FXO F6 - English | y | 7G/Gg1 JXG F6 - English | 70E KM F6 - English | k | |
3 | 70I LJR F4 - English | 7o/Pe2 AN | N | 7O1/Ma IC F4 - English | N | 7o/Es1 TD F4 - English | n | 70I LJR F4 - English | k |