Question about Microsoft Excel for PC

1 Answer

Using * in a EXCEL functions

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

Posted by on

1 Answer

  • Level 3:

    An expert who has achieved level 3 by getting 1000 points

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Master:

    An expert who has achieved Level 3.

  • Master
  • 404 Answers

Correction to your code
=COUNTIF(M2,"*Fox*")

version of formula that will work
=IF(COUNTIF(M2,"*FOX*"),"yes","no")

Posted on Jan 08, 2009

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Count number of Delivery Status


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.

Jun 18, 2009 | Microsoft Excel for PC

Tip

HOW TO COUNT THE OCCURRENCES OF A TEXT STRING


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*")

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

2 Answers

Hi, how do I find the 1st 2 letters in a cell and reference it to another. ie A1: 0209JJP001. I thus want to find "02" which is Feb (this would be a vlookup I assume). Therefore the 1st 2 letters...


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

1 Answer

Excel formula


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

2 Answers

School Truancy Report Formula


For the same exemple you can try this:
=COUNTIF(A1:A4,"=George Washington School")

Apr 03, 2009 | Microsoft Excel for PC

1 Answer

Countifs


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)

Jan 21, 2009 | Microsoft Office Excel 2003 for PC

3 Answers

Excel Formula


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.

Apr 22, 2008 | Microsoft Excel for PC

1 Answer

Excel and the AVERAGE function


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.

Dec 30, 2007 | Business & Productivity Software

1 Answer

Multiple letters in each cell, want to add the letters and tally how many of each


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

Oct 31, 2007 | Microsoft Excel for PC

1 Answer

Excel


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

Not finding what you are looking for?
Microsoft Excel for PC Logo

256 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18304 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...