This is a common mistake that even experienced Excel users make when using COUNTIFS (and its counterpart, SUMIFS which you can learn about here). Unfortunately, this formula will return a value of 0, as shown here: You might try this formula using COUNTIFS: For example, suppose we want to count the number of times either Desktop or Server appear in the Product column. =COUNTIFS( B2:B13,"Desktop", C2:C12,"Monitor") Scenario 3 - COUNTIFS with two criteria in the same criteria rangeĪnother common scenario is to use the same criteria with more than one criteria_range. Here's an example where the second criteria range is smaller than the first: 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. =COUNTIFS(B2:B13,"Desktop",C2:C13,"Monitor")Īs you can see, this formula has returned the correct number of rows where both criteria are matched in their respective columns.
Once you understand how to use COUNTIFS with one critera_range and criteria, it becomes easy to extend it to two or more criteria_range/criteria combinations.įor example, let's count the number of times the combination of Desktop and Monitor appear in our spreadsheet:
Scenario 2 - COUNTIFS with two criteria in different criteria ranges COUNTIFS will simply count all of the cells in the range you specify that contain the criteria you specify. It would also work if we used a criteria range that was, say, 2 columns wide and 12 rows deep, or even 12 columns wide and 1 row deep. Our example above uses a criteria range that is 1 column wide and 12 rows deep (B2:B13). Notice that although we don't need to use COUNTIFS in this scenario, COUNTIFS will return the same result.Īlso note that it is possible to use any rectangular criteria range. Here's how that might look in our spreadsheet: This can be done with a COUNTIF function - we don't need to use COUNTIFS at this point.
Suppose we want to count how many times Desktop appears in the Product column. Let's say we have a list of recent computer hardware purchases as shown here:
Scenario One - COUNTIFS with one criterion This is best demonstrated with an example. It's important to note that the COUNTIFS function takes all of the criteria_range/criteria pairs and counts only those instances where all of the pairs are true. Whether you have two or twenty criteria ranges, each one MUST be the same shape.You can use up to XX criteria_range and criteria pairs in a single COUNTIFS function.There are several things you need to remember when using the COUNTIFS function in a formula: Similarly, criteria_range2 is the second range of cells that should be matched against criteria2.criteria1 is the criteria should be matched against criteria_range1.criteria_range1 is the first range of cells.
=COUNTIFS(criteria_range1,criteria1, criteria_range2,criteria2.) The COUNTIFS function has the following syntax: It is similar to SUMIFS, which will find the sum of all cells that match a set of multiple criteria. COUNTIFS extends the COUNTIF function which only allows one criteria. The COUNTIFS function in Excel counts the number of cells in a range that match a set of multiple criteria.