How do I count multiple conditions in Excel?
Show COUNTIF Function with Multiple Criteria (Table of Contents)
Countif function in excel is used to count the cell numbers in any given range and specifying the criteria. But what if we have multiple criteria to be counted in the same set of data. This can be done when we use one CountIf function with another one separated by a plus sign. Plus, sign this allows us to use multiple CountIf functions in any way. Also, we use multiple criteria in a single Countif function in curly brackets separated by commas. There can be two ways for multiple counting:
COUNTIF Formula with Multiple CriteriaBelow is the COUNTIF Formula : The COUNTIF Formula has two arguments, i.e. range, criteria.
Here in this article, we will discuss the COUNTIF Function with multiple criteria in the same column. As we know, the COUNTIF function is a built-in function in excel, and it falls under the FORMULAS tab. Thus, the COUNTIF function can be entered in a cell-like below screenshot: How to Use COUNTIF Function with Multiple Criteria?It is very simple and easy to use. Let us understand the working of the COUNTIF Function by some COUNTIF Formula example. Example #1We have given some product list: We need to find out the count of Laptop & Mobile. As these products are in the same column, hence we will use the COUNTIF function here. We have given two conditions; hence the COUNTIF formula will apply as follows: = COUNTIF(range, criteria1) + COUNTIF(range, criteria2) + ……. Please refer to the below screenshot: Explanation:
The final result is: Example #2We have given some product details here. In this example, we will use a COUNTIF function between two numbers. Here we will calculate the count of items which is greater than 12 and less than 30. The COUNTIF formula will apply as follows: =COUNTIF( $C$19:$C$30, “>12”) + COUNTIF($C$19:$C$30, “<30”) Please refer to the below screenshot: Explanation:
Hence the final result is: Example #3Let’s take the same product details again. Here, we will calculate the sum of products who sold out in the month of Jan, Feb & May. We will apply the COUNTIF formula as follows: =COUNTIF($D$40: $D$51, “Jan”) + COUNTIF( $D$40:$D$51,”Feb”) + COUNTIF( $D$40:$D$51, “May”) Explanation:
The Final Result is: Things to Remember
Recommended ArticlesThis has been a guide to COUNTIF with Multiple Criteria. Here we discuss the COUNTIF Formula with Multiple Criteria and how to use the COUNTIF Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
Can I use Countif with multiple criteria?You can use the COUNTIFS function in Excel to count cells in a single range with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.
Can you use Countif for 3 criteria?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.
How do I count multiple criteria in one column?If there are more than two criteria that you want to count in one column, just use =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)+…
How do you count cells with conditions?COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. A number representing cells counted.
|