CR4® - The Engineer's Place for News and Discussion®


Previous in Forum: Is Windows 7 Intentionally Difficult To Update?   Next in Forum: W 10 Loves You in a New and Different Way
Close
Close
Close
10 comments
Active Contributor

Join Date: Jul 2016
Posts: 11

CountIf Function Not Counting All the Values in Range

10/28/2016 3:15 AM

I am not an expert in excel. Please help me out with Countif formula. I am putting the correct formula, correct range and correct text value as criteria. But somehow the result donot show all the counts for selected criteria. Please help me out.

Register to Reply
Pathfinder Tags: Countif criteria Excel range
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

These comments received enough positive ratings to make them "good answers".

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
2
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9551
Good Answers: 455
#1

Re: COUNTIF function not counting all the values in range

10/28/2016 4:46 AM

We can't help, because we can't see from here what you're trying to do.

How do we know you've put in the correct formula, range and text values when we don't know what any of it is?

__________________
"Love justice, you who rule the world" - Dante Alighieri
Register to Reply Good Answer (Score 2)
Guru
Engineering Fields - Electrical Engineering - Been there, done that, still doing it. Engineering Fields - Control Engineering - New Member

Join Date: Dec 2008
Location: Long Island NY
Posts: 12215
Good Answers: 774
#2

Re: COUNTIF function not counting all the values in range

10/28/2016 9:28 AM

One quirk I found in Excel is if the range contains discontinuous cells (empty row or column) the range will be truncated at that empty cell that is part of the empty row or column.

__________________
"Don't disturb my circles." translation of Archimedes last words
Register to Reply Score 1 for Good Answer
Guru

Join Date: Aug 2005
Location: Hemel Hempstead, UK
Posts: 4136
Good Answers: 230
#3

Re: Countif Function Not Counting All the Values in Range

10/28/2016 12:44 PM

Do a little screen capture like this:-

So that we can see your range and formula.

If the range is too big, then, keep halving it 'til you find a small area where the discrepancy lies. By then you will probably have spotted the problem, but, if not post back here.

As you can see Excel counted "bob" as well as "Bob", which surprised me a bit.

__________________
We are alone in the universe, or, we are not. Either way it's incredible... Adapted from R. Buckminster Fuller/Arthur C. Clarke
Register to Reply
Guru
Technical Fields - Technical Writing - New Member Engineering Fields - Piping Design Engineering - New Member

Join Date: May 2009
Location: Richland, WA, USA
Posts: 19978
Good Answers: 749
#4
In reply to #3

Re: Countif Function Not Counting All the Values in Range

10/28/2016 12:56 PM

Next question: What if you want it to be case-sensitive?

__________________
In vino veritas; in cervisia carmen; in aqua E. coli.
Register to Reply
Guru

Join Date: Aug 2005
Location: Hemel Hempstead, UK
Posts: 4136
Good Answers: 230
#6
In reply to #4

Re: Countif Function Not Counting All the Values in Range

10/28/2016 1:27 PM

Why isn't that just a qualifier on the countif function?

Why do you need an extra pair of seemingly unnecessary parentheses?

And, why do you need the -- before the exact?

I do have to reluctantly admit that Excel is probably the best spreadsheet out there, but, all the same some things do seem unnecessarily complicated.

__________________
We are alone in the universe, or, we are not. Either way it's incredible... Adapted from R. Buckminster Fuller/Arthur C. Clarke
Register to Reply
Guru
Technical Fields - Technical Writing - New Member Engineering Fields - Piping Design Engineering - New Member

Join Date: May 2009
Location: Richland, WA, USA
Posts: 19978
Good Answers: 749
#7
In reply to #6

Re: Countif Function Not Counting All the Values in Range

10/28/2016 1:40 PM

That sounds like one of those historical problems where you have to ask Charles Simonyi, or even go all the way back to Lotus 123.

__________________
In vino veritas; in cervisia carmen; in aqua E. coli.
Register to Reply
Guru
Engineering Fields - Civil Engineering - Member

Join Date: Mar 2011
Location: ''but, don't we get PAID to ask questions?...''
Posts: 920
Good Answers: 11
#10
In reply to #6

Re: Countif Function Not Counting All the Values in Range

10/31/2016 6:21 PM

By the way, if you use the data in Post #6,

and enter =countif($D1:$D14,"*OB*")

making sure to capitalize OB,

in Cell E1, say, you'll get the total of the

(Bobs) + (Rob) + (Robert) + (Bobby)

= ( 3 ) + ( 1 ) + ( 1 ) + ( 1 ) = 6 ...

__________________
''illigitimi non carborundum...'' (i.e.: don't let the fatherless ones grind you down...)
Register to Reply Score 1 for Off Topic
Guru

Join Date: Apr 2010
Posts: 5744
Good Answers: 576
#5

Re: Countif Function Not Counting All the Values in Range

10/28/2016 1:07 PM
Register to Reply
Power-User

Join Date: Jan 2010
Location: Dayton Ohio
Posts: 134
Good Answers: 4
#8

Re: Countif Function Not Counting All the Values in Range

10/28/2016 2:41 PM

Look at how the value is placed in the cell (justified)

It may be a text value in a otherwise numeric column

__________________
MikeMack747
Register to Reply
7
Guru
Hobbies - Musician - Engineering Fields - Chemical Engineering - New Member Engineering Fields - Control Engineering - New Member Engineering Fields - Instrumentation Engineering - New Member

Join Date: Jan 2007
Location: Moses Lake, WA, USA, Thulcandra - The Silent Planet (C.S. Lewis)
Posts: 3929
Good Answers: 174
#9

Re: Countif Function Not Counting All the Values in Range

10/28/2016 4:57 PM

Everything you need to know to use this function:

COUNTIF function

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

Note: The COUNTIF function does not directly support data or ranges based upon the background color or font color of data cells. Excel supports the definition of User-Defined Functions (UDFs) using the Microsoft Visual Basic for Applications Editor, to support for Excel operations on cells and tables employing color for data marking. (Press Alt+F11 to open the Editor.)

Syntax

COUNTIF(range, criteria)

For example:

  • =COUNTIF(A2:A5,"apples")
  • =COUNTIF(A2:A5,A4)

Argument name

Description

range (required)

The group of cells you want to count.

Range can contain numbers, arrays, a named range, or references that contain numbers. Blank and text values are ignored.

Learn how to select ranges in a worksheet.

criteria (required)

A number, expression, cell reference, or text string that determines which cells will be counted.

For example, you can use a number like 32, a comparison like ">32", a cell like B4, or a word like "apples".

COUNTIF uses only a single criteria. Use COUNTIFS if you want to use multiple criteria.

Examples

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Data

Data

apples 32
oranges 54
peaches 75
apples 86

Formula

Description

=COUNTIF(A2:A5,"apples") Counts the number of cells with apples in cells A2 through A5. The result is 2.
=COUNTIF(A2:A5,A4) Counts the number of cells with peaches (using criterion in A4) in cells A2 through A5. The result is 1.
=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) Counts the number with oranges (using criterion in A3) and apples (using criterion in A2) in cells A2 through A5. The result is 3. This formula uses two COUNTIF expressions to specify multiple criteria, one criteria per expression.
=COUNTIF(B2:B5,">55") Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2.
=COUNTIF(B2:B5,"<>"&B4) Counts the number of cells with a value not equal to 75 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5,"<>75"). The result is 3.
=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85") Counts the number of cells with a value greater than (>) or equal to (=) 32 and less than (<) or equal to (=) 85 in cells B2 through B5. The result is 3.
=COUNTIF(A2:A5,"*") Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4.
=COUNTIF(A2:A5,"?????es") Counts the number of cells that have exactly 7 characters that end with the letters "es" in cells A2 through A5. The question mark (?) is used as the wildcard character to match individual characters. The result is 2.
=COUNTIF(fruit,">=32")-COUNTIF(fruit,">85") Uses a named range (applied to cells A2:A5) to count the number of cells with a value greater than 55 in cells B2 through B5. The result is 2.

Common Problems

Problem

What went wrong

Wrong value returned for long strings. The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters.

To match strings longer than 255 characters, use the CONCATENATE function or the concatenate operator &. For example, =COUNTIF(A2:A5,"long string"&"another long string").

No value returned when you expect a value. Be sure to enclose the

criteria argument in quotes.

A COUNTIF formula receives a #VALUE! error when referring to another worksheet. This error occurs when the formula that contains the function refers to cells or a range in a closed workbook and the cells are calculated. For this feature to work, the other workbook must be open.

Best practices

Do this

Why

Be aware that COUNTIF ignores upper and lower case in text strings.

Criteria aren't case sensitive. In other words, the string "apples" and the string "APPLES" will match the same cells.

Use wildcard characters. Wildcard characters —the question mark (?) and asterisk (*)—can be used in

criteria. A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) in front of the character.

For example, =COUNTIF(A2:A5,"apple?") will count all instances of "apple" with a last letter that could vary.

Make sure your data doesn't contain erroneous characters. When counting text values, make sure the data doesn't contain leading spaces, trailing spaces, inconsistent use of straight and curly quotation marks, or nonprinting characters. In these cases, COUNTIF might return an unexpected value.

Try using the CLEAN function or the TRIM function.

For convenience, use named ranges COUNTIF supports named ranges in a formula (such as =COUNTIF(

fruit,">=32")-COUNTIF(fruit,">85"). The named range can be in the current worksheet, another worksheet in the same workbook, or from a different workbook. To reference from another workbook, that second workbook also must be open.

__________________
"Reason is not automatic. Those who deny it cannot be conquered by it. Do not count on them. Leave them alone." - Ayn Rand
Register to Reply Good Answer (Score 7)
Register to Reply 10 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

These comments received enough positive ratings to make them "good answers".

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Copy to Clipboard

Users who posted comments:

JohnDG (1); MikeMack747 (1); Mikerho (1); MR. Guest (1); Randall (2); redfred (1); Rixter (1); Tornado (2)

Previous in Forum: Is Windows 7 Intentionally Difficult To Update?   Next in Forum: W 10 Loves You in a New and Different Way

Advertisement