How to Count number of text instances in excel?

excel excel-2013
Erfan Jazeb Nikoo picture Erfan Jazeb Nikoo · Apr 21, 2013 · Viewed 22.9k times · Source

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?

Answer

Siddharth Rout picture Siddharth Rout · Apr 21, 2013

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

Related questions

If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
Excel: Searching for multiple terms in a cell

I use this handy equation to search for a term inside of a cell in excel. =IF(ISNUMBER(SEARCH("*Gingrich*",C1)),"1","") This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1. All I'd like …

Read More
Clear contents and formatting of an Excel cell with a single command

If you want to clear the contents of a cell or range in Microsoft Excel, you can use .ClearContents. If you also want to clear the formatting, you can use .ClearFormats. Sheets("Test").Range("A1:C3").ClearContents Sheets("Test").Range("…

Read More