QUESTION

Taking a range of cells that contain a text "smoke" in several cells that will add into one cell with a number value ie if A1,A3,A5 have the word "SMOKE" in cell C1 would equal 3. and if A2,A4 have the word "HEAT" in cell C2 would equal 2.

ANSWER

The formula is ...

=COUNTIF(A:A,A1)

Put it in B1 and copy it to the rest of column B.