Count Based on Condition with Eight Examples

In this post you are going to learn how to count when there is the condition, so for this, we will use Function COUNTIF and COUNTIFS with four Examples of Each.

The key examples which will you cover here are:

Examples of COUNTIF

    •  Return Text with the count of cells
    • Count Negative Number
    • Count Cells Excluding Error
    • Count Duplicates values in multiple rows

Examples of COUNTIFS

    •  Count Days Between Dates
    • Count One Column based on other
    • Count After matching all Column
    •  Count how many times certain character appear.

Download Your Example Excel File

But before going to Examples, let’s understand what is COUNTIF Function.

The Excel COUNTIF function returns a count of numbers based on given criteria.

Parameters of countif are:

range – The range of cells to evaluate the conditions (criteria)
criteria – The rule to apply for additio.

Now, let’s work around three Excel COUNTIF’s example on a worksheet.

Remember: You can also evaluate the formula using a shortcut F9 function key or under the formulas, tab click on Evaluate formula.

To Evaluate

Return Text with the count of cells

In this article, you are going to learn how to count cells with repetitive Text. In case you want to show text with number of occurrences.

Query: In this example, we have two columns of Items and Count. Our task is to know how many times similar items come in multiple rows to give the cumulative number in column C.

Formula to implement:

=COUNTIF($B$3:B3,B3)&””&B3

Return count with name of Items.
Return count with name of Items.

Result: 1 Duck Tape

                (Count = 1)

It counts based on criteria (B3) then return text with count (1 Duck Tape) in column C (Count).

Similarly, in column C it will return the count of each item with Name. like in cell C6 it returns (2 Duck Tape) because Duck Tape comes two times.

Remember: Please go to how to count only Numbers or Text in a column.

 

Note: :  ‘&’ operator in Excel is a type of concatenate which is used to join two strings, values or functions.

Count Negative Numbers

After reading this article you will be able to count negative numbers and zeros  excluding positive numbers.

Query: In this example, we have two columns of Student and Marks. Our task is to know how many student get negative marks.

Formula to implement:

=COUNTIF(C3:C12,”<0″)

Count Cells those contain Negative Numbers.
Count Cells those contain Negative Numbers.

Result: 3

It first checks the given number in the range (C3 to C12) as 3 Student marks are negative(in column C) so, it returns 3 ignoring positive numbers.

Remember: Same way you can give count of positive number instead “<0” put “>0” in Formula.

 

Count Cells Excluding Error

When you have a column where some cells are filled with error and remaining with text. To count text after excluding (#N/A)  NA() value.

Query: In this example, we have two columns of Name and Department. Our task is to know how many people are still working in each department.

Formula to implement:

=COUNTIF(C3:C21,”<>#N/A”)

Counting after excluding #N/A value
Counting after excluding #N/A value

Result: 15

It returns counts in the given range (C3 to C21) after excluding #N/A values in the cells.

Note: COUNTIF is a combination of COUNT and IF Excel functions.

Count Duplicates values in multiple rows

To give count of the column with two different criteria then later add them by counting how many times they (both) come in the range.

Query: In this example, we have two columns of Food items and Payment Mode. Our task is to know how many Food items have been paid.

Formula to implement:

=COUNTIF(C3:C13,C3)+COUNTIF(C3:C13,C4)

Add and Count Two different Text.
Add and Count Two different Text.

Result: 7

It will first check both criteria (Cash and Online) and then give the count of both (4 and 3) in the given range (C3 to C13) and then add them.

Note: You can use COUNTIF function to count when there are two different texts in the same range.

Now let’s understand what is COUNTIFS function in Excel before going with four examples.

The Excel COUNTIFS function returns a count of numbers based on one or multiple given criteria. COUNTIFS can be used to count values when selected cells meet criteria based on dates, numbers, and text.

The COUNTIFS has the following arguments:

range1 – The first range to evaluate containing criteria1
criteria1 – The criteria to apply for addition in range1
range2 [optional] – The second range to evaluate containing criteria2
criteria2 [optional] – The criteria to apply for addition in range2

Now, let’s see how we can use the Excel COUNTIFS function on the real-time worksheet to find a count of cells that meet all of the criteria.

Count Days Between Dates

In case you want to know how many days between two dates, in the array to get the count.

Query: In this example, we have two columns of Date and Code. Our task is to know how many days are greater than or equals to start date and less than or equals to the end date.

Formula to implement:

=COUNTIFS(B:B,”>=”&$E$8,B:B,”<=”&$E$9)

Counting days greater or equal to start date and less and equal to end date.
Counting days greater or equal to start date and less and equal to end date.

Result: 20

It checks both criteria (“>=” and “<=”) then gives the count of dates which are greater than equals to start date but less than or equals to end dates.

Note: COUNTIFS is a combination of COUNT and IFS Excel functions.

Count One Column based on other

In case there is matching text in both column and you want to count only a specific text of both in the range.

Query: In this example, we have two columns of Region and Item Category. Our task to count the only Region with North and Item Category with Office Supplies.

Formula to implement:

=COUNTIFS(B3:B21,B4,C3:C21,C4)

To get count of north with office supplies
To get count of north with office supplies

Result: 4

It will first check both criteria North (in column B) in the range (B3 to B21) and Office Supplies (in column C) in the given range (C3 to C21) to give count.

Count After Matching all Column

When you have multiple criteria and to know count based on different rows or columns (greater or less than).

Query: In this example, we have three columns of Order, Quantity, and Price. Our task is to get count of Order “Burger” whose Quantity is less than 10 and Price is less than 100.

Formula to implement:

=COUNTIFS(B3:B18,B4,C3:C18,”<10″,D3:D18,”<100″)

Count of burger with quantity and price less than 10 and 100 respectively.
Count of burger with quantity and price less than 10 and 100 respectively.

Result: 2

It first checks three criteria order of Burger (in column B) with Quantity less than 10 (in column C) and Price less than 100 (in column C) in the given range to give count.

Note: COUNTIFS can check up to 20 criteria with criteria range.

Count how many times certain character appear.

Counting characters match with certain start and end letters. For this, we use wildcard symbol (*) asterisk that gives us enough flexibility to returns the results that are alike.

Query: In this example, we have two columns of Furniture and Quantity. Our task is to count furniture which starts with character C and ends with character S.

Formula to implement:

=COUNTIFS($B$3:$B$13,”C*”,$B$3:$B$13,”*S”)

To count character which start with C and end with S.

Result: 3

It will count furniture that starts with character C and end character S in the given range (B3 to B13).

You can also learn how to Sum, and Average based on condition with examples.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments