Essentially, I am having trouble finding the formula to perform a countif in an array for text that partially comprises other cells in the array.
As an example, when searching for text "fire" in an array of "fire", "arcade fire", "firetruck", and "water", I'd want the formula to return a value of three. Have tried using countif and match to no avail.
Code I've tried includes: MATCH("FIRE",B1:B4,1) where B1:B4 are the above strings. COUNTIF("FIRE",B1:B4). =IF(ISNUMBER(SEARCH("FIRE",B1:B4)),1,0) (this function is not ideal as it would not be counting).