How to Count number of text instances in excel?

Question

I have a table of my workdays and I want to count instances of word 'work' in each row. I have a table like this:

I used this code in J1 cell but it doesn't work.

=SUM(IF(2:2 = "Sleep",1,0))

I have found this formula in microsoft's website but it doesn't work. What is causing this problem?

Answer 1

You need to use the COUNTIF function.

=COUNTIF(C2:I2,"Sleep")

This goes in Cell J2

From Excel's Help

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify.

When in doubt, press the magic button F1 in Excel. :)