For example in cell M2 there is the wording Quick Brown Fox Jumps Over The Lazy Dogs

Using COUNTIF(M2,="*FOX*"") produces a count of 1

Using IF(M2="*FOX*","YES","NO") produces NO as the result - I would have expect YES.

I was under impression that using *either side* of characters would work for IF function.

Your comments would be appreciated - thank you

Correction to your code

=COUNTIF(M2,"*Fox*")

version of formula that will work

=IF(COUNTIF(M2,"*FOX*"),"yes","no")

Posted on Jan 08, 2009

To count textual values, use the countif function; the syntax is =countif(range,criteria). So, if your range, for example is B3 through B100 and you want to know how many cells contain the status value Delivered, put the following in the cell you want the count to show: =countif(b3:b100, "Delivered"). Do the same thing for each of your other status values in the cell that you want the count to appear.

Note that this requires each status value to be spelled correctly in each cell. I'd recommend assigning shorter values, say 1 through 5 or A through E to avoid that -- unless the values are being generated automatically so the spelling is guaranteed correct.

Note that this requires each status value to be spelled correctly in each cell. I'd recommend assigning shorter values, say 1 through 5 or A through E to avoid that -- unless the values are being generated automatically so the spelling is guaranteed correct.

Jun 18, 2009 | Microsoft Excel for PC

In the cell that you
want the result to appear in, enter the appropriate formula from the following
examples.

How to Count the Occurrences of a Number

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

How to Count the Occurrences of a Text String

Method 1

Use this formula

=SUM(IF(range="text",1,0))

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*")

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

=COUNTIF(a1:a10,"?x*")

How to Count the Occurrences of a Number

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

How to Count the Occurrences of a Text String

Method 1

Use this formula

=SUM(IF(range="text",1,0))

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*")

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

=COUNTIF(a1:a10,"?x*")

on Nov 11, 2013 | Microsoft Excel Business & Productivity...

To reference the first letters in a cell, use the left function. The syntax is =left(cell,#). So, to return the left two letters from cell A1, you use =left(a1,2). You can put that in a cell or incorporate it into some functions.

Jun 12, 2009 | Microsoft Excel for PC

Use the COUNTIF command. The COUNTIF command can count the criteria for a range of cells. Since you can only use it for one range of cells or criteria, you simply add another criteria to the formula as follows: =COUNTIF(AG1:AG5,"X")+COUNTIF(Sheet2!L1:L6,"X")

Apr 10, 2009 | Microsoft Excel for PC

For the same exemple you can try this:

=COUNTIF(A1:A4,"=George Washington School")

=COUNTIF(A1:A4,"=George Washington School")

Apr 03, 2009 | Microsoft Excel for PC

The formula in both versions is the same:

=countif(range,value)

i.e. The below formula will increase its count by one if any of the 18 cells it's checking are equal to 1.

=countif(A1:B9,1)

=countif(range,value)

i.e. The below formula will increase its count by one if any of the 18 cells it's checking are equal to 1.

=countif(A1:B9,1)

Jan 21, 2009 | Microsoft Office Excel 2003 for PC

Lets say your data (cars) is in range "D1:D21".

You need to know how many are Dodge and Ford in cell E1.

=COUNTIF(D1:D21,"Dodge") + COUNTIF(D1:D21,"Ford)

Hope this helps.

You need to know how many are Dodge and Ford in cell E1.

=COUNTIF(D1:D21,"Dodge") + COUNTIF(D1:D21,"Ford)

Hope this helps.

Apr 22, 2008 | Microsoft Excel for PC

Here is one way:

In this example, my numbers are in cells a1 through a4. My average is computed with the formula:

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

I'm summing the range of numbers and dividing that by the count of nonzero numbers. With this formula, you'll get an error if there are no nonzero numbers.

In this example, my numbers are in cells a1 through a4. My average is computed with the formula:

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

I'm summing the range of numbers and dividing that by the count of nonzero numbers. With this formula, you'll get an error if there are no nonzero numbers.

Dec 30, 2007 | Business & Productivity Software

Use the =COUNTIF function

For example if column a has the letters in it:

=COUNTIF(a1:a100,"a")

or

@COUNTIF(a1..a100,"a")

This will count all of the letter 'a' s in

column a from row 1 to row 100.

Mike

For example if column a has the letters in it:

=COUNTIF(a1:a100,"a")

or

@COUNTIF(a1..a100,"a")

This will count all of the letter 'a' s in

column a from row 1 to row 100.

Mike

Oct 31, 2007 | Microsoft Excel for PC

See if this is possible using the COUNTIF formula in Excel.
Example:
My range is A1:A20. Lowest number being 1 and highest number being 20.
To see how many are less than 10 I'd use:
=COUNTIF(A1:A20,"<=10")
To see how many are less than 20 but greater than 10 is a little different. (And this might be the crazy way). I do this:
=SUM(COUNTIF(A1:A300,"<=20") - COUNTIF(A1:A300,"<=10"))
Try this and see if it works for you. It seemed to work fine for me.

Sep 23, 2007 | Microsoft Office Standard for PC

Jan 28, 2016 | Microsoft Excel for PC

256 people viewed this question

Usually answered in minutes!

×