by The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel based on AND as well as OR logic. You will find a number of examples for different data types - numbers, dates, text, wildcard characters, non-blank cells and more.
Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria.
The difference is that COUNTIF is designed for counting cells with a single condition in one range, whereas COUNTIFS can evaluate different criteria in the same or in different ranges. The aim of this tutorial is to demonstrate different approaches and help you choose the most efficient formula for each particular task.
Excel COUNTIFS function - syntax and usage
The Excel COUNTIFS function counts cells across multiple ranges based on one or several conditions. The function is available in Excel 365, 2021, 2019, 2016, 2013, Excel 2010, and Excel 2007, so you can use the below examples in any Excel version.
COUNTIFS syntax
The syntax of the COUNTIFS function is as follows:
- criteria_range1 (required) - defines the first range to which the first condition (criteria1) shall be applied.
- criteria1 (required) - sets the condition in the form of a number, cell reference, text string, expression or another Excel function. The criteria defines which cells shall be counted and can be expressed as 10, "<=32", A6, "sweets".
- [criteria_range2, criteria2]… (optional) - these are additional ranges and their associated criteria. You can specify up to 127 range/criteria pairs in your formulas.
In fact, you don't have to remember the syntax of the COUNTIF function by heart. Microsoft Excel will display the function's arguments as soon as you start typing; the argument you are entering at the moment is highlighted in bold.
Excel COUNTIFS - things to remember!
- 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.
- Each additional range must have the same number of rows and columns as the first range (criteria_range1 argument).
- Both contiguous and non-contiguous ranges are allowed.
- If the criteria is a reference to an empty cell, the COUNTIFS function treats it as a zero value (0).
- You can use the wildcard characters in criteria - asterisk (*) and question mark (?). See this example for full details.
How to use COUNTIFS and COUNTIF with multiple criteria in Excel
Below you will find a number of formula examples that demonstrate how to use the COUNTIFS and COUNTIF functions in Excel to evaluate multiple conditions.
How to count cells with multiple criteria (AND logic)
This scenario is the easiest one, since the COUNTIFS function in Excel is designed to count only those cells for which all of the specified conditions are TRUE. We call it the AND logic, because Excel's AND function works this way.
Formula 1. COUNTIFS formula with multiple criteria
Suppose you have a product list like shown in the screenshot below. You want to get a count of items that are in stock (value in column B is greater than 0) but have not been sold yet (value is column C is equal to 0).
The task can be accomplished by using this formula:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
And the count is 2 ("Cherries" and "Lemons"):
Formula 2. COUNTIFS formula with two criteria
When you want to count items with identical criteria, you still need to supply each criteria_range / criteria pair individually.
For example, here's 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 "Grapes" have "0" value in both columns.
Using a simpler formula with a single criteria_range like COUNTIFS(B2:C7,"=0") would yield a different result - the total count of cells in the range B2:C7 containing a zero (which is 4 in this example).
How to count cells with multiple criteria (OR logic)
As you have seen in the above examples, counting cells that meet all of the specified criteria is easy because the COUNTIFS function is designed to work this way.
But what if you want to count cells for which at least one of the specified conditions is TRUE, i.e. based on the OR logic? Overall, there are two ways to do this - by adding up several COUNTIF formulas or using a SUM COUNTIFS formula with an array constant.
Formula 1. Add up two or more COUNTIF or COUNITFS formulas
In the table below, supposing you want to count orders with the "Cancelled" and "Pending" status. To have it doen, you can simply write 2 regular Countif formulas and add up the results:
=COUNTIF($C$2:$C$11,"Cancelled") + COUNTIF($C$2:$C$11,"Pending")
In case each of the functions is supposed to evaluate more than one condition, use COUNTIFS instead of COUNTIF. For example, to get the count of "Cancelled" and "Pending" orders for "Apples" use this formula:
=COUNTIFS($A$2:$A$11, "Apples", $C$2:$C$11,"Cancelled") + COUNTIFS($A$2:$A$11, "Apples", $C$2:$C$11,"Pending")
Formula 2. SUM COUNTIFS with an array constant
In situations when you have to evaluate a lot of criteria, the above approach is not the best way to go because your formula would grow too big in size. To perform the same calculations in a more compact formula, list all of your criteria in an array constant, and supply that array to the criteria argument of the COUNTIFS function. To get the total count, embed COUNTIFS inside the SUM function, like this:
In our sample table, to count orders with the status "Cancelled" or "Pending" or "In transit", the formula would go as follows:
=SUM(COUNTIFS($C$2:$C$11, {"cancelled", "pending", "in transit"}))
In a similar manner, you can count cells based on two or more criteria_range / criteria pairs. For instance, to get the number of "Apples" orders that are "Cancelled" or "Pending" or "In transit", use this formula:
=SUM(COUNTIFS($A$2:$A$11,"apples",$C$2:$C$11,{"cancelled","pending","in transit"}))
You can find a few more ways to count cells with OR logic in this tutorial: Excel COUNTIF and COUNTIFS with OR conditions.
How to count numbers between 2 specified numbers
By and large, COUNTIFS formulas for numbers fall into 2 categories - based on several conditions (explained in the above examples) and between the two values you specify. The latter can be accomplished in two ways - by using the COUNTIFS function or by subtracting one COUNTIF from another.
Formula 1. COUNTIFS to count cells between two numbers
To find out how many numbers between 5 and 10 (not including 5 and 10) are contained in cells C2 through C10, use this formula:
=COUNTIFS(C2:C10,">5", C2:C10,"<10")
To include 5 and 10 in the count, use the "greater than or equal to" and "less than or equal to" operators:
=COUNTIFS(B2:B10,">=5", B2:B10,"<=10")
Formula 2. COUNTIF formulas to count numbers between X and Y
The same result can be achieved by subtracting one Countif formula from another. The first one counts how many numbers are greater than the lower bound value (5 in this example). The second formula returns the count of numbers that are greater than the upper bound value (10 in this case). The difference between the first and second number is the result you are looking for.
- =COUNTIF(C2:C10,">5")-COUNTIF(C2:C10,">=10") - counts how many numbers greater than 5 and less than 10 are in the range C2:C10. This formula will return the same count as shown in the screenshot above.
- =COUNTIF(C2:C10, ">=5")-COUNTIF(C2:C10, ">10") - the formula counts how many numbers between 5 and 10 are in the range C2:C10, including 5 and 10.
How to use cell references in COUNTIFS formulas
When using logical operators such as ">", "<", "<=" or ">=" together with cell references in your Excel COUNTIFS formulas, remember to enclose the operator in "double quotes" and
add an ampersand (&) before a cell reference to construct a text string.
In a sample dataset below, let's count "Apples" orders with amount greater than $200. With criteria_range1 in cells A2:A11 and criteria_range2 in B2:B11, you can use this formula:
=COUNTIFS($A$2:$A$11, "Apples", $B$2:$B$11, ">200")
Or, you can input your criteria values in certain cells, say F1 and F2, and reference those cells in your formula:
=COUNTIFS($A$2:$A$11, $F$1, $B$2:$B$11, ">"&$F$2)
Please notice the use of absolute cell references both in the criteria and criteria_range arguments, which prevents the formula from being broken when copied to other cells.
For more information about the use of an ampersand in COUNTIF and COUNTIFS formulas, please see Excel COUNTIF - frequently asked questions.
How to use COUNTIFS with wildcard characters
In Excel COUNTIFS formulas, you can use the following wildcard characters:
- Question mark (?) - matches any single character, use it to count cells starting and/or ending with certain characters.
- Asterisk (*) - matches any sequence of characters, you use it to count cells containing a specified word or a character(s) as part of the cell's contents.
Tip. If you want to count cells with an actual question mark or asterisk, type a tilde (~) before an asterisk or question mark.
Now let's see how you can use a wildcard char in real-life COUNTIFS formulas in Excel. Suppose, you have a list of projects in column A. You wish to know how many projects are already assigned to someone, i.e. have any name in column B. And because we are learning how to use the COUNTIFS function with multiple criteria, let's add a second condition - the End Date in column D should also be set.
Here is the formula that works a treat:
=COUNTIFS(B2:B10,"*",D2:D10,"<>"&""))
Please note, you cannot use a wildcard character in the 2nd criteria because you have dates rather that text values in column D. That is why, you use the criteria that finds non-blank cells: "<>"&""
COUNTIFS and COUNTIF with multiple criteria for dates
The COUNTIFS and COUNTIF formulas you use for dates are very much similar to the above formulas for numbers.
Example 1. Count dates in a specific date range
To count the dates that fall in a certain date range, you can also use either a COUNTIFS formula with two criteria or a combination of two COUNTIF functions.
For example, the following formulas count the number of dates in cells C2 through C10 that fall between 1-Jun-2014 and 7-Jun-2014, inclusive:
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/7/2014")
=COUNTIF(C2:C9, ">=6/1/2014") - COUNTIF(C2:C9, ">6/7/2014")
Example 2. Count dates with multiple conditions
In the same manner, you can use a COUNTIFS formula to count the number of dates in different columns that meet 2 or more conditions. For instance, the below formula will find out how many products were purchased after the 20th of May and delivered after the 1st of June:
=COUNTIFS(C2:C9, ">5/1/2014", D2:D9, ">6/7/2014")
Example 3. Count dates with multiple conditions based on the current date
You can use Excel's TODAY() function in combination with COUNTIF to count dates based on the current date.
For example, the following COUNTIF formula with two ranges and two criteria will tell you how many products have already been purchased but not delivered yet.
=COUNTIFS(C2:C9, "<"&TODAY(), D2:D9, ">"&TODAY())
This formula allows for many possible variations. For instance, you can tweak it to count how many products were purchased more than a week ago and are not delivered yet:
=COUNTIFS(C2:C9, "<="&TODAY()-7, D2:D9, ">"&TODAY())
This is how you count cells with multiple criteria in Excel. I hope you will find these examples helpful. Anyway, I thank you for reading and hope to see you on our blog next week!
2039 comments
Hi Svetlana
I am trying to count the number of time a postcode is used within some 35,000 records. The postcodes have the format 'AB10 9BC' and I only wish to count the first group of figures. Using =COUNTIF(G:G,"AB10*")seems to work fine BUT.... Some of the first group of numbers consist only of three digits eg 'AB1 9BC'. This means that counting for AB1* also includes and counts AB10/AB11/AB12 etc etc. How can I count only AB1 postcodes in this example? Hope that makes sense and I look forward to your reply.
Hi,
I am trying to count the number of days marked "Holiday" in a set range but only those with a date less than 31/05/2014 but greater than 31/03/2014. The date range is in column R with the text fields in columns T:X.
The other point is that the date column only has a week commencing date, not a date for every cell.
I have tried =COUNTIFS($R:$R, "<="&I30,$T:$X, "Holiday")(where I30 is a date field with 31/05/2014). This produces an #VALUE! error. I haven't tried to enter the additional date criteria as I wanted to get the formula to work first.
Taking each part in isolation (=COUNTIF($R:$R, "<="&I300 and =COUNTIF($T:$X, "Holiday")) works perfectly but not combined.
Can anyone point me in the direction of where I'm going wrong?
use formula in countifs best multiple condition apply one time
i have a question, i am making a personnel planning for my employees. This is based on competences this means that specific people are trained to do job A but they can not do job B. I have 2 tables. One table "Training" with the name of the person and in the same row for which jobs they are trained so job A, job B, job D. Then i have the real personnel planning. I have on top which job needs to be executed so for example job A. Hence I put a name of the person. I want to have the cell highlighted in Red if the person is not trained for job A. I think to do it via COUNTIF but i am confused as I have different criteria, i need to refer first to job A, hence i need to search if that specific person is trained for job A. Can somebody help me please?
Hi, for my case, cell A1 has an entry of 19/11/14 and cell B1 has an entry of 25/11/14. 25/11/14 is more than 19/11/14 by 6 days. I would like to set the formula for the dates that are more than 5 days to be false. Which means the entry above is false because it is 6 days.
And for the entries that are less than or equal to 5 days to be True.
I have only one column
how to count how many row have time 7 am to 8 am and next 8 am to 9 am and next?
7:31:11
7:48:41
7:51:35
7:59:49
8:02:01
8:03:47
8:06:41
8:07:16
8:07:47
8:15:20
8:19:43
8:29:16
8:33:42
9:09:05
9:09:36
9:11:56
9:19:27
9:19:55
9:20:25
9:26:13
9:34:33
9:35:19
9:47:05
9:49:42
10:10:10
10:10:24
10:10:37
10:10:59
10:11:10
10:11:22
10:11:43
10:12:07
10:12:29
10:12:42
10:13:04
10:13:25
10:14:05
Dear All
I need urgent help on one formula. For Eg
A B C D E
AXT-11232 2,500 89369 sent
I want in column E the current date if any of the cells like A, B, C or D are filled. My data will be in the same format as in A, B, C. I just want the current DATE , whenever data has been entered in any of the cells marked as A, B , C or D
I like to count First five character(number) as a individual number in a column but in different rows and same numbers count once only. How can I do this in excel? Fore example;
A B
98765_A1A2A3 ISO
98765_A4 AATCC
87654_A2A3 AATCC
92345_A1A3 ISO
=LEN(ABS(LEN(E3)+SUBSTITUTE(LEFT(E3,5),E3,1))-15)
I like to count First five character as a individual number like; 98765_A3(98765),98765_A6(98765),87654_A2,(87654),92345_A1(92345) in a column but in different row and same numbers count once only. How can I do this?
Hello,
My countif formula returns 0 if it does not anything that matches the criteria. Is there a way to set up the formula so that if it doesn't find anything that matchs the formula returns "-" instead of 0.
Thank you
Hi! Here's the data I want to process. I want to count the number of entries a specific person submitted that matches anything except Not Qualified.
DECLINED Peñalosa, Joyce Ann
NOT QUALIFIED Calvo, Vivian
CALLBACK Roda, Scepter John
NOT QUALIFIED Henardino Jr., Edwin
NOT QUALIFIED Asentado, Darell
CALLBACK Peñalosa, Joyce Ann
DECLINED Roda, Scepter John
CALLBACK Alipio, Marisel
DECLINED Emeterio, Absalon
NOT QUALIFIED Solomon, Cinderella
NOT QUALIFIED Solomon, Cinderella
CALLBACK Toring, Jared
CALLBACK Henardino Jr., Edwin
NOT QUALIFIED Viscayno, Mabel
NOT QUALIFIED Viscayno, Mabel
DECLINED Solomon, Cinderella
SCHEDULED Roda, Scepter John
CALLBACK Emeterio, Absalon
NOT QUALIFIED Henardino Jr., Edwin
NOT QUALIFIED Trocio, Princess Joy
CALLBACK Baruel, Coleen Grace
CALLBACK Baruel, Coleen Grace
CALLBACK Baruel, Coleen Grace
NOT QUALIFIED Toring, Jared
SCHEDULED Solomon, Cinderella
SCHEDULED Gamboa, Estrella
DECLINED Peñalosa, Joyce Ann
DECLINED Calvo, Vivian
NOT QUALIFIED Alipio, Marisel
CALLBACK Baruel, Coleen Grace
CALLBACK Arizobal, Argin
DECLINED Calvo, Vivian
NOT QUALIFIED Arizobal, Argin
CALLBACK Toring, Jared
DECLINED Emeterio, Absalon
SCHEDULED Peñalosa, Joyce Ann
NOT QUALIFIED Henardino Jr., Edwin
Hello Shwan,
Here you go:
=COUNTIF($B$1:$B$100, "Person Name") - COUNTIFS($B$1:$B$100, "Person Name", $A$1:$A$100, "Not Qualified")
I want to be able to check if data in one cell in column a = x and then if so count the data in adjacent cell in column b.
To explain in more detail, I am creating a statistics chart where commissioners need to be able to be able to compare data for region 1 to region 2 and region 3.
So column A will contain region keys such as 1, 1, 1, 2, 3, 3 then in column B will be ages. So if column A contains a 1 collect data in cell adjacent to the cell it is counting.
Hi,
I want to create a formula that counts how many of the letter 'y' occurs in particular columns but not in a range. The columns I want to search are D2, F2, H2, J2, L2, N2, P2, R2, T2, V2. The problem I'm encountering is that I don't want to search E2, G2, I2, ... I then want to have this formula for all rows in the spreadsheet. I don't see the answer above ... Thanks!
Hi,
Below is my issue. I have a few statuses which can be used to a closed ticket. I want to count all the closed tickets in the worksheet using the status. I used below formula but it did not work. Please help..
COUNTIFS(A2:A1000,"A",B2:B1000,"Night",C2:C1000, OR("Solved","Cancelled",....))
Department Shift Status
A Night Solved
A Night Cancelled
Thanks in Advance!
Chalinda
Sorry it hasn't posted correctly - just looks a mess
Hi Wayne,
Regrettably, our blog engine is not perfect... If you can send us your sample workbook at support@ablebits.com, it will be easier for us to figure out the task.
Hi, hope you can help. I'm trying to get a cell give me a figure of the following(Cell A is First Aid Course and Cell I says they attended or not attended) - I don't want it to count if the cell is blank (so basically a upcoming course)
Example
Course Title Start Date End Date Tutor Customer Name Attended?
First Aid 01/11/14 14/11/14 John Paul Jones Attended
First Aid 01/11/14 14/11/14 John Sarah Smith Attended
First Aid 01/11/14 14/11/14 John Mo Ali FTA
Food Safety 11/11/14 15/11/14 Lee Paul Jones Attended
Food Safety 11/11/14 15/11/14 Lee Sharon Jones FTA
First Aid 24/11/14 31/11/14 John Steve Bi
First Aid 24/11/14 31/11/14 John Simon Gee
Result to go into this type of box
Course Name Total Referred Attended FTA
First Aid ? ? ?
Food Safety ? ? ?
I can send on the sheet if its easier.
Thanking you in advance
regards
wayne
i need a formula to automatically check mark i.e(ü) mark a cell(eg:A1) if another cell (eg:H1)contains number:1
Condition is H1 may contain any numbers like 1,2,5,6,8 to 20
A1 should check mark only if there is 1
( if i drag formul A2 should check mark only if there is 2...and process upto A20 i.e number 20)
Formula should come like this:
(A1:A20) should Check H1 if perticular number contains in that cell i.e
check H1 if it contains 1,3 then tickmark A1 A3
check H1 if it contains 2, 20 then tickmark A2 A20
check H1 if it contains 3 then tickmark A3
upto 20numbers
Formula should come like this:
(B1:B20) should Check H2 if perticular number contains in that cell i.e
check H2 if it contains 1,3 then tickmark B1 B3
check H2 if it contains 15 then tickmark B15
check H2 if it contains 18 then tickmark B18
Upto 20 numbers
IF IT IS NOT POSSIBLE TO USE NUMBERS IN SINGLE CELL
I CAN TAKE H1 I1 J1...
Hello Wilfy,
If you can send your workbook with the source data and the result you want to get to support@ablebits.com, our support team will try to help you.
If a worksheet arranged as mentioned below, HOW CAN COUNT HOW MANY CT BECOME BETWEEN AGE OF 21 TO 25. I HAD TRIED TO CALCULATE THIS IN ANOTHER SHEET BY USING FORMULA =COUNTIFS('NOMINAL ROLL '!A:A,"=CT",'NOMINAL ROLL '!C:C,">=21",'NOMINAL ROLL '!C:C,">=25"). BUT NOT SHOWING ACTUAL RESULT. REQUEST INTIMATE A SUITABLE FORMULA TO SOLVE THE THIS PROBLEM, PLEASE.
NOMINAL ROLL (SHEET-1)
A B C
RANK NAME AGE
CT Vinod Singh 22
CT Rajesh Kumar Purohit 24
CT VIJAY KUMAR 25
SI CHANDAN KR PASWAN 25
CT MANTU KUMAR RAM 23
CT MITHILESH KUMAR JH 24
CT PURUSHOTAM KUMAR 23
CT AJAY MANDAL 27
SI KAILASH SOREN 29
CT DEBASHIS SHIL 27
SI CHHOTU UJMODAK 30
CT DIPAK TIGGA 27
CT SUMANTA KR DAS 30
CT SAGAR DHAMALA 28
HC SANDEEP KUMAR 25
CT RAYAZ AHMAD 23
CT MONIRATH MONDAL 23
CT BABLU NAIK 23
HC PRADIP MONDAL 23
CT TAPASA ROY 25
Hi Satheesh,
Your formula looks correct, just replace >=25 with <=25 :)
Hi,
How can I solved to multiple word in a criteria from single word in a excel file. pls help me
Hello Rasel,
I am sorry, your task is not clear. If you can describe it in more detail and give some example, we'll try to help.
Thanks for your replay......
Actually I would like to say,
which formula should I use from multiple condition but value will be text.
For example: sumifs functions.
If I use to sumifs formula then it result any data but it not be text.....
Please help me if you cooperated me..
Hi,
Column A: dates
Column B: numbers
I would like to calculate the number of times values in column B within a specified range occur within a specified date range. I've emailed an example file to support.
Any help would be much appreciated.
Many thanks,
Dan
Hi Dan,
I believe the following formula will work for you:
=COUNTIFS($B$2:$B$75, ">=6", $B$2:$B$75, "<=7", $A$2:$A$75, ">="&F2, $A$2:$A$75, "<="&G2)
I emailed you the worksheet with the formulas a moment ago. Hopefully, this is what you are looking for.
Hi, i have some formula populated column.
I was trying to do a COUNTIFS(,"") to find the data which condition false.
But i understood that COUNTIFS takes the formula as a value and gives a erratic value.
Can you guide?