How to use the COUNTIFS function
The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.
It allows you to use up to 254 arguments or 127 criteria pairs.
Formula in cell E3:
Lucy and 5 are found twice, in row 3 and 7, the function returns 2 in cell E3.
Excel Function Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Arguments
criteria_range1 | Required. The cell range you want to count the cells meeting a condition. |
criteria1 | Required. The condition that you want to count. |
[criteria_range2] | Optional. Additional ranges, up to 127 pairs. |
[criteria2] | Optional. Additional ranges, up to 127 pairs. |
Comments
The optional wildcard characters make the COUNTIFS function even more powerful, these characters are * asterisk and question marks ? The question mark ? matches a single character while the * asterisk matches any sequence of characters even 0 (zero) characters.
The really interesting stuff happens when you use the function in an array formula. The number of values in each criteron argument determines how many values the function returns, however, all criteria arguments must use the same number of conditions. If one value is used, like in example 1 and 2. A single value is returned.
In examples 3 and 4, five values are used in each criterion argument and the COUNTIFS function returns five values. Remember to enter the formulas as an array formula.
Example 1 - Using two conditions
This example demonstrates the COUNTIFS function with two conditions applied to two separate columns. The COUNTIFS function counts a record when both conditions are met on the same row.
This formula checks if text string "AA" is found in cell range B9:B13 and if 10 is found in cell range C9:c13. Row 9 and 12 contain both conditions and 2 are returned to cell G9.
COUNTIFS(B9:B13,E9,C9:C13,F9)
becomes
COUNTIFS({"AA";"BB";"CC";"AA";"BB"},"AA",{10;20;30;10;40},10)
and returns 2 in cell G9.
The calculation above shows two arrays and ou can tell that by the curly brackets {}.
The values in those arrays are separated by a semicolon meaning the values are on a row each.
Here is a post where I use this technique: Highlight duplicate rows
Example 2 - Logical operators
The following formula counts how many times text string "Han" equals a cell value in cell range C9:C13, it also checks if dates in cell range B9:B13 are larger than or equal to February 1st, 2013 and smaller than or equal to February 28, 2013.
Three conditions in total applied to two cell ranges, this also demonstrates that you can use logical operators with conditions. Make sure you use double-quotes around the logical operators and an ampersand to concatenate the values with the logical operators.
Here is a list of all logical operators you can use and their combinations.
- = equal to
- < less than
- > larger than
- <= less than or equal to
- >=larger than or equal to
- <> not equal to
COUNTIFS(B9:B13, ">="&E9, B9:B13, "<="&F9, C9:C13, G9)
becomes
COUNTIFS({41275; 41307; 41324; 41336; 41325}, ">="&41306, {41275; 41307; 41324; 41336; 41325}, "<="&41333, {"Luke"; "Han"; "Ben"; "Luke"; "Han"}, "Han")
and returns 2 in cell E12. Row 9 and 13 have the word "Han" and is in month February 2013.
Here is a post where I use comparison operators: Filter overlapping date ranges
Example 3 - Count duplicate records
The following array formula counts each cell value in each row and returns an array of values.
I demonstrated in example 1 and 2 above how to use a single condition in each criteria argument, the formula above demonstrates what happens if you use multiple conditions.
Note, you will receive an error if you don't use the same number of conditions in each criteria argument.
COUNTIFS(B9:B13, B9:B13, C9:C13, C9:C13)
becomes
COUNTIFS({"Asia"; "Africa"; "Asia"; "Asia"; "Asia"},{"Asia"; "Africa"; "Asia"; "Asia"; "Asia"},{"Luke"; "Han"; "Ben"; "Luke"; "Han"},{"Luke"; "Han"; "Ben"; "Luke"; "Han"})
and returns this array {2; 1; 1; 2; 1} in cell range D9:D13. This array is interesting because it identifies how many times each record occurs in the data set.
Here are two posts where I use this technique:
Example 4 -
In this example, I am using a single cell value as a criteria_range argument and a cell range as criteria argument. This may seem confusing but it is definitely possible and sometimes very useful.
COUNTIFS(B17,B9:B13,C17,C9:C13)
becomes
COUNTIFS("Asia", {"Asia"; "Africa"; "Asia"; "Africa"; "Australia"}, "Ben", {"Luke"; "Han"; "Ben"; "Luke"; "Han"})
and returns {0; 0; 1; 0; 0} in cell range D9:D13.
'COUNTIFS' Function examples
The following 51 articles have formulas containing the COUNTIFS function.
This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Table of Contents Find closest value How to enter an array formula Find closest value - Excel 365 Find closest […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B […]
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
The image above demonstrates a Conditional Formatting formula that highlights unique distinct records. This means that the first instance of […]
This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]
The Excel defined table contains start and end dates for each date range in cell range A3:B10. Cell B13 is […]
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]
Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]
This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]
This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
Question: Column B has dates Column C as data B5 : 1/1/2010 : 5000 B6 : 2/1/2010 : 4000 B7 […]
This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]
This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]
This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]
This article demonstrates how to sort a table based on count meaning the formula counts each record and returns a […]
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Answer: Array formula in […]
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]
Functions in 'Statistical' category
The COUNTIFS function function is one of many functions in the 'Statistical' category.
One Response to “How to use the COUNTIFS function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[…] COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...) Counts the number of cells specified by a given set of conditions or criteria […]