COUNT, COUNTIF & COUNTIFS Functions in Microsoft Excel

Brief Introduction

           Microsoft Excel COUNT, COUNTIF & COUNTIFS Functions are mostly used to count the cells, range, one or multiple criteria, these functions used the logical operator & wildcards (*,?) for partially matching.

          

         The Microsoft Excel COUNT Function is the most popular member of the counting cells or ranges (which contains the numerical data) functions family. It comes in handy when we have to count cells or ranges which contain the numerical data, those cells which contain text etc. aren’t considered in counting.

          

         The Microsoft Excel COUNTIF Function is also one of the member of the counting cells or ranges based on one criteria functions family, this one criteria may be date, text, number or other condition & it also support the logical operator & wildcard (*,?) for partially matching.

       

       As well as COUNT & COUNTIF Functions, the Microsoft Excel COUNTIFS Function is another most popular counting cells function, which based on multiple criteria, these multiple criteria maybe dates, texts, numbers or other conditions & it also support the logical operator & wildcard (*,?) for partially matching.

Purpose:

To count the cells, ranges, one or multiple criteria.

Syntaxes of COUNT, COUNTIF & COUNTIFS Functions:

The syntaxes of COUNT, COUNTIF & COUNTIFS Functions in Excel are as follows; 

• =COUNT(value1, [value2],…)
• =COUNTIF(range, criteria)
• =COUNTIFS(criteria_range1, criteria1, …)

Return value:

The number of times criteria are met.

Arguments: 

• Value – numerical figure in one or multiple cells.
• Range - The range of cells for evaluation.
• Criteria - The criteria which we apply on the range.

Logical operators

          When you are using a test with Microsoft Excel COUNTIF & COUNTIFS Functions, we can use any of the following logical operators:

Comparison operator	Meaning	Example
=	equal to	A1=D1
>	greater than	A1>D1
>=	greater & or equal to	A1>=D1
<	less than	A1<d1
<=	less than & or equal to	A1<=D1
<>	not equal to	A1<>D1

You can download free the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below download icon,

   

          

          This article explains in simple terms how to use COUNT, COUNTIF & COUNTIFS Functions. It takes a step-by-step approach, & following the below contents, we will touch in this post.

1. Microsoft Excel COUNT Function explanation through various examples.
2. Microsoft Excel COUNTIF Function explanation through example.
3. Microsoft Excel COUNTIFS Function explanation through example.

Let’s start to learn something new about the COUNT, COUNIF & COUNTIFS Functions of Microsoft Excel.                

1.  Microsoft Excel COUNT Function - Example 1.

         Microsoft Excel COUNT Function is used to count cells or ranges which contains the numerical data, those cells which contains text etc. aren’t consider in counting, so now we explain COUNT Function through various examples, as shown in below example,

Explanation: - In this example we have taken the information of a company which have A to I Projects, from "Row 2" to "Row 11" which contains the information of Project Names, Start day, End day, Progress & the last "F" column contains a formula that how we can use the COUNT Function to get our specific return. As we see that the cell "F8" contains the COUNT Function & "B8, C8, D8 & E8” are our given information, as we see that cell "B8" contains text, cell "C8" Contains the numerical figure & cells "D8" and "E8" are haven’t contained any data, so according to COUNT Function the result is "1" because in the selected range only one cell contains the numerical data.                

In this scenario, we have used a formula like below,

=COUNT(B8:E8)

Example 2.

               If in the case when we use COUNT Function vertically, so then see the below example,

Explanation: - In this example we see that from "Row 2" to "Row 11" which contains the information of Project Names, Start day, End day, Progress & the last "Row 13" contains formula that how we can use the COUNT Function to get our specific return. As we see that the cell "C13" contains the COUNT Function & "C3:C11” are our given information, as we see that cells "C4 & C7" are haven’t contained any data while other cells Contain the numerical figures, so according to COUNT Function the result is "7" because in selected range "C3:C11” only 7 cells contains the numerical data.

In this scenario, we have used a formula like below,       

=COUNT(C3:C11)

2. Microsoft Excel COUNTIF Function.

          Microsoft Excel COUNTIF Function is used to count cells or ranges based on one criteria, this one criteria maybe a date, text, number or other condition & it also support the logical operator & wildcard (*,?) for partially matching, see the below example,

Explanation: - In this example we have taken the information of a School monthly receipt of the fee record, from "Row 2" to "Row 11" which contains the information of Month, Student Name, Father Name, Fee Paid & the last cell "H6" contains a formula that how we can use the COUNTIF Function to get our specific return. As we see that the cell "H6" contains the COUNTIF Function, "C3:C11" is our range & "G6” is our given criteria, as we see that our criteria meet four times in selected range, so according to COUNTIF Function the result is "4" because in selected range the criteria "Mohammad" meet four times.

In this scenario, we have used a formula like below,

=COUNTIF(C3:C11,G6)

3. Microsoft Excel COUNTIFS Function.

          Microsoft Excel COUNTIFS Function is used to count range based on multiple criteria, these multiple criteria may be dates, texts, numbers or other conditions & it also supports the logical operator & wildcard (*,?) for partially matching, as shown below example, 

Explanation: - In this example we see that from "Row 2" to "Row 11" which contains the information of Month, Student Name, Father Name, Fee Paid & the last cell "I6" contains formula that how we can use the COUNTIFS Function to get our specific return. As we see that the cell "I6" contains the COUNTIFS Function, "C3:C11" & "D3:D11" are our two ranges & "G6” & "H6” are our given criteria, as we see that our criteria meet Five times in selected ranges, so according to COUNTIFS Function the result is "5" because in selected ranges the criteria "Mohammad" & "Hashim" meet five times.

In this scenario, we have used a formula like below,

=COUNTIFS(C3:C11,"Mohammad",D3:D11,H6)