Hi guys
I have a formula =SUM((IF(IFERROR(SEARCH("yes",'DIARY BCH'!2:2),0)<>0,1,0))*(IF(IFERROR(SEARCH("*mask*",'DIARY BCH'!$1:$1),0)<>0,1,0))) (array) that counts all the cells in a row that contain the text "yes" AND in the heading of the column of that cell with the "yes" contains the text "mask". This appears to be working fine (see yellow cell in attached example).
I now need to add an additional criterion to this formula, where, in the cell that contains the text "yes" and has the column heading containing the text "mask", the formula checks five columns back on that row, to see if that cell contains the text "indoor", and then counts this.
So, for example, patient C007 in the attached example should count 4.
I've tried all kinds of tricks with MATCH, but can't seem to get it working.
Any help please?
Thank you
Bookmarks