How to Use COUNTIF With Multiple Criteria in Excel You use the COUNTIF function in Excel when you want to count cells based on a single criterion. If you want to count based on multiple criteria, you will use the COUNTIFS function. Here is how to make the best use of this function. Using COUNTIF with multiple criteria Syntax for a single criteria is: =COUNTIF(range, criteria) The best function you can use for situations when you need to count cells based on multiple criteria is the COUNTIFS function. The “s” on the end of Countif makes it plural, and that means that there are multiple criteria. You can use up to XX criteriarange and criteria pairs in a single COUNTIFS function, each criterion ranges one MUST be the same shape.
The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets a single or multiple criteria. The COUNTIFS function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function. In the above formula: A2:A15 is the data range that you want to use, KTE and KTO are the criteria that you want to count. 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)+.
You should note that the COUNTIFS function takes all of the criteriarange/criteria pairs and counts only those instances where all of the pairs are true. COUNTIFS formula with the same criteria If you want to count items with identical criteria, you still need to supply each criteriarange / criteria pair individually. For example, here is the right formula to count items that have 0 both in column B and column C: =COUNTIFS($B$2:$B$7,'=0', $C$2:$C$7,'=0') This COUNTIFS formula returns 1 because only “Manuel” have “0” value in both columns. If you use simple formula with a single criteriarange like COUNTIFS(B2:C7,”=0″) would show a different result – the total count of cells in the range B2:C7 containing a zero (which is 4 in this example).
![Excel Excel](http://futuro24kilates.club/wp-content/uploads/2018/05/excel-countif-unique-unique-excel-excel-count-unique-names-steps-for-using-advanced-filters-function-for-counting-unique-unique-excel-excel-countif-unique-dates.jpg)
![Excel Countif Multiple Criteria Excel Countif Multiple Criteria](/uploads/1/2/5/6/125618595/406396173.png)
COUNTIFS with two criteria in different criteria ranges When you understand how to use COUNTIFS with one criterarange and criteria, then it becomes easy to extend it to two or more criteriarange/criteria combinations. For example, let’s count the number of times the combination of Start and Finish appear in our spreadsheet: =COUNTIFS(B3:B14,'Start',C3:C14,'Finish') As you can see, this formula has returned the correct number of rows where both criteria are matched in their respective columns. It’s worth repeating one of the rules of COUNTIFS here – the two criteria ranges must be the same shape and size or your formula won’t work, and will return a #VALUE error. Here’s an example where the second criteria range is smaller than the first: =COUNTIFS(B2:B13,'Start',C2:C12,'Finish') COUNTIFS with two criteria in the same criteria range Another way to use COUNTIFS is to use the same criteria with more than one criteriarange. For example, suppose we want to count the number of times either Start or Progress appears in the Product column.
You can try this formula using COUNTIFS: =COUNTIFS(B2:B13,'Start',B2:B13,'In Progress') Unfortunately, this formula will return a value of 0, as shown here: Common errors with COUNTIFS This is a common mistake that even experienced Excel users make when using COUNTIFS (and its counterpart, SUMIFS which you can learn about here). The problem is that COUNTIFS looks in our criteriarange, B2:B13 and tries to count cells that contain both Start and Progress. Remember that we wanted to count cells that contain either Start or Progress. Of course, there are no cells in our range that contain both Start and Progress, so the result is 0. Ironically, the best way to find the answer in this scenario is so simple that it is often overlooked – just use two COUNTIF functions in your formula, rather than once COUNTIFS function, like this: =COUNTIFS(B2:B13,'Start')+COUNTIF(B2:B13,'Progress') In other words, we simply count all the rows that contain Start, and add those to the tally of rows that contain Progress.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live here for some 1 on 1 help. Your first session is always free.