4

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: Simple Work Sheet

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?

Conscript
  • 607
  • 6
  • 21
Erfan Jazeb Nikoo
  • 978
  • 3
  • 12
  • 36

2 Answers2

7

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. :)

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • just FYI, if you use Excel in a different language the command might be different @see: http://en.excel-translator.de/countif/ – tpei Dec 05 '13 at 11:22
0

Just came across a similar thing in a worksheet and came to google before I remembered why it didn't work.

The countif statement above is perfectly fine, however the original formula given wouldn't have worked as to use a sum in this way means you have to make it an array or CSE Formula instead.

So, if you come across this, click in the formula bar and press ctrl + shift + enter and it should sort the whole thing out.

Robert Morrison
  • 1