QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to COUNTIF in MS Excel 2016?

By Neha T Leave a Comment

Have you ever operated the function COUNTIF in Excel? COUNTIF is a statistical function which counts all the cells that match the criteria we describe in the function. This function always returns you a count number that has met the criteria specified in the function.

Let us discuss the syntax of the COUNTIF function:

COUNTIF(range, criteria)

range: Here, the range denotes the range of cells you are selecting for finding the match with the criteria.

criteria: It is the condition you describe in the formula which is matched with the range of cells specified in the formula.

Further, we will see how we can mention the criteria in the various method:

  1. Text
  2. Number
  3. Cell
  4. Logical Operator
  5. & Operator
  6. Wildcard Character
  7. Not Equal To
  8. COUNTIF Blank
  9. COUNTIF Not Blank
  10. COUNTIF with multiple criteria

For the explanation, I have taken data of 17 staff members (A2:A18) and their sale of this month (B2:B18). This picture below shows the data that we have used for explaining the COUNIF function with various criteria.

Database for Countif

Steps to use the Function COUNTIF in Excel 2016:

COUNTIF in Excel with Text

In the given database I have to check how many times the text “Charles’ appears.

For this the formula will be:

=COUNTIF(A2:A18, “Charles”)

Result = 4

Countif with text value 3

COUNTIF in Excel with Numbers

If I have to count cells that have the value 100.

For this, my formula will be

=COUNTIF(B2:B18, 100)

Result = 6

Remember for the numeric value, we do not require double-quotes.

Countif with number value 2

COUNTIF in Excel with Cells

In case I have to count, how many time content in particular cell appear in the range of selected cell?

Then my formula will be:

=COUNTIF(A2:A18, A4)

Result = 4

In cell A4 I have the text content, ‘Charles’. So the above formula will give the count of the number of times Charles appear in the selected range.

Countif with Cell value 2

COUNTIF with Logical Operator

COUNTIF greater than

For checking that in the numerical data ranged from B2:B18; how many cells contain the number that is greater than 160?

So the formula for this is:

=COUNTIF(B2:B18, “>160”)

Result = 8Countif with Logical operator 2

COUNTIF with greater than equal to

If we have to find the cells that have the value greater than and equal to 160 then;

The formula will be:

=COUNTIF(B2:B18, “>=160”)

Result = 10

Similarly, we can check for smaller than, smaller than equal to.

Countif with Logical operator 1

COUNTIF with & operator

The “&” operator merges two criteria into a single criterion for validating the result. For explaining this we have an example in the picture below:

In the following example, we have counted only those cells which are “>” and also are equal to 160.

The formula is:

=COUNTIF(B2:B18, “>”&160)

Result is 8

Countif with & operator 2

Wildcard Character

? Character

The “?” character is a wildcard character which is used to match the individual character. For understanding this look at the example in the picture below.

The COUNTIF formula will only count the cells which will have 7 characters and must be ending with the sequence of character “les”. We have four cells in the selected range which have seven characters word ending with “les” i.e. “Charles”.

Formula is

COUNTIF(A2:A18, “????les”)

Result is 4

Countif with question operator 2

It’s not that you can only count the cells who have words ending with the particular character sequence. You can even find the cells that have words starting with particular character.

Like for the formula

=COUNTIF(A2:A18, “Emi???”)

Result is 2

* Character

Like “?” operator, “*” is also a wildcard character. But the difference is that “*” character matches any character. We can understand it with an example.

If we have to count the cells that have word with any character in any sequence then the formula is:

=COUNTIF(A2:A18, “*”)

As we have 17 cells in the selected range and all cells have a staff name. Therefore the result is 17.

Countif with asteric value 2

To count the cells who have the ending character “es” then the formula would be:

=COUNTIF(A2:A18,”*es” )

This formula will count all the cells who have the word ending with “es”.

Countif with asteric value 1

COUNTIF For Not Equal To

For “not equal to” the sign used is “<>”. If I have to find how many cells have the number that is not equal to 160. Then my formula would be:

=COUNTIF(B2:B18, “<>160”)

Result = 15

Countif with Not equal to

COUNTIF if Blank

Suppose we have to count the number of cells that are blank and doesn’t have any content in it. For this the formula would be:

=COUNTIF(A2:A18, “<>”&“*”)

The formula says that count the cells that have content not equal to any character.

Result = 2

Countif blank

COUNTIF not Blank

To count the number of cells that are not blank has some value either text, number, date, etc. then the formula would be:

=COUNTIF(A2:A18, “<>”&“”)

Result 15

Countif not blank

COUNTIF with Multiple Criteria

In the COUNTIF function, only one criterion can be specified. So, if we have to use COUNTIF with multiple criteria then we can use multiple COUNTIF; mentioning one criterion per COUNTIF function.

In the picture below we have the example where we want the count of cells which have content either Charles or Emiliy

Formula is

=COUNTIF(A2:A18, “Charles”)+COUNTIF(A2:A18, “Emiliy”)

Result is 6

Countif multiple criteria

So this is all about COUNIF function. Hope you find it knowledgeable. Keep reading.

You Might Also Like:

count unique values featured imageHow to Count Unique Values in Excel 2016? Sumif feature imageHow to SUMIF in Excel? Calculate Average in Excel feature imageHow to Calculate Average in Excel? Subtract feature image 1How to Subtract in Excel? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016? enable macro featured imageHow to Enable Macros in Excel 2016? Calculate percentage Featured ImageHow to Calculate Percentage in MS Excel 2016? Delete Duplicates feature imageHow to Delete Duplicates in Excel 2016?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to delete Bookmarks in Google Chrome?
  • How to Create an Outlook Account?
  • How to change fonts in gmail
  • How to Add or Sum in MS Excel 2016?
  • How to Download and Save Instagram Stories?

Copyright © 2023 · QueHow · Contact Us