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
Svetlana and team – I enjoyed reading your blog about the “COUNTIFS” function, but am having problems using it. I have a worksheet that I want to count the number of unduplicated rows with two different criteria. I want to count the number of completed actions by analyst, but only count each class code once. The class code is in column “G”, the analyst (there are five different analysts) is in column “C”, and the analysis/recommendation is in column “AF”. When an analysis is completed for a particular class code, the column “AF” has some text. If the analysis is not complete, “AF” is empty.
I can successfully count the number of unduplicated class codes in my worksheet, by using:
=SUM(IF(FREQUENCY(MATCH(G2:G7497,G2:G7497,0),MATCH(G2:G7497,G2:G7497,0))>0,1))
My worksheet has 7,495 rows of duplicated class codes and 3.666 rows of unduplicated class codes.
I can successfully count the number of analyses completed by analyst, by using:
=COUNTIFS(C:C,"LF",(AF:AF),"")
What I’m trying to do, but can’t figure out is how to use the COUNTIFS function and count the UNDUPLICATED number of analyses completed.
Can you help?
Hello Lorna,
You can use the following array formula, please don't forget to press Ctrl + Shift + Enter to complete it:
{=SUM((IF(FREQUENCY(MATCH(G2:G7497,G2:G7497,0),MATCH(G2:G7497,G2:G7497,0))>0,1,0))*(--(AF2:AF7498<>"")))}
But I'd recommend adding a helper column and copying the class codes there according to your conditions, e.g. here is the formula for row 2:
=if(AF2<>"", G2, "")
Then copy this formula down to the other cells in the helper column and count using your first formula. This seems to be a quicker and more flexible approach.
Svetlana - it WORKED! Many many thanks from California! Where do I send the Bailey's????
Thank you, Lorna. A virtual bottle will suffice : )
I'm working from a spreadsheet and pulling data from the spreadsheet into a table on a new worksheet. I need to countif the data from A2:A114 is greater than 12/31/2013 and less than 4/1/2014 to obtain a count for Q1. I need to do this for each Quarter and change the dates which is working fine.
For example: =COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013")-COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">3/31/2014")
Now I need to know of the data returned from the above formula which of those have Cleared using values in columns W2:w114 and the word "cleared". I tried this one and it is not yielding a realistic number.
Getting error:
=COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013")*COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,"<4/1/2014")
Is it possible for me to achieve what I need?
Thanks
Monique
Hello Monique,
If I understand the task right, the following formula will work a treat:
=COUNTIFS('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013",'Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">3/31/2014", 'Partnership Tracking '!$W$2:'Partnership Tracking '!$W$114,"cleared")
If it doesn't, you can send us your sample workbook at support@ablebits.com and we will try to help.
I have tried using the same multiple if's with a table that has multiple data validation lists. The user simply picks under the source column where the lead came from and then in the type column they would pick what type it came in as.
My formula is =countif(Table1[Source],"Kijiji",Table1[Type],"phone") to find out what percentage of leads were sourced from kijiji by phone as opposed to kijiji by email or our website by phone.
Not sure what I am doing wrong because it keeps throwing it out saying it is causing arguments.
Any help would be appreciated.
Hi Jason,
You were almost there. It just should be COUNTIFS rather than COUNTIF because you count by 2 conditions:
=COUNTIFS(Table1[Source],”Kijiji”,Table1[Type],”phone”)
Hello,
=COUNTIFS('Current Enlisted By Name'!$E:$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!$D:$D,"WHQK"&"*")
I need that formula above to capture these UICs only:
WHQKA0,WHQKB0,WHQKC0,WHQKE0,WHQKT0,WHQLC0
Please help
Hello Rich,
This can be done either with a helper column or VBA.
If adding a helper column is acceptable, say column K and copy the following formula there, starting from row 2:
=OR(D2="WHQKA0",D2="WHQKB0",D2="WHQKC0",D2="WHQKE0",D2="WHQKT0",D2="WHQLC0")
Your main formula will be:
=COUNTIFS('Current Enlisted By Name'!$E:$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!$K:$K,TRUE)
Hi,
I'm creating a spread sheet to work with attendance numbers for an after school program. I'm using this formula for each grade.
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")
I want to copy the formula across a spread sheet so that it will calculate daily attendance for each class. To do this, I need to be able to get the row to flow like this:
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!F:F,"1")
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!G:G,"1")
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!H:H,"1")
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!I:I,"1")
But instead, it copies like this:
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")
=COUNTIFS(DailyAtten!D:D,B6,DailyAtten!F:F,"1")
=COUNTIFS(DailyAtten!E:E,C6,DailyAtten!G:G,"1")
=COUNTIFS(DailyAtten!F:F,D6,DailyAtten!H:H,"1")
=COUNTIFS(DailyAtten!G:G,E6,DailyAtten!I:I,"1")
Is there a way for me to set this up so that only the DailyAtten! value progresses when I continue the formula?
Thank you!
Hello Lindsey,
Just enter A6 as an absolute reference - $A$6 - and Excel won't change the address when you copy the formulas to other cells.
For more details, please see:
https://www.ablebits.com/office-addins-blog/relative-absolute-cell-references-excel-conditional-formatting/
Hi,
In a coloumn, there are 4500 cells that contains values like -20000,-1000,100,200,500,1500 etc so i want to count there are how many 100s,200s,300s,-100s etc but if i use "COUNTIF" formula i have to give every time its very time taking.
So plz tell me formula that gives the count there are how many 100s,200s etc are there
Hi Subbu,
- Insert a pivot table (select your column and go to Insert > Tables > PivotTable).
- Place the pivot table onto a new sheet.
- Drag and drop your column from "Choose fields to add to report" to the Rows section
- Drag and drop your column from "Choose fields to add to report" to the Values section.
- Click on the column name in the Values section and select "Value field settings" from the drop-down menu.
- Select "count" instead of "sum" and click OK.
If you want to update information, right-click on the pivot table report and select "Refresh" from the context menu.
Name Gender Religion DIT0101 DIT0102 DIT0103 DIT0104 DIT0105 TDH 1100 total Average Grade Award
Jane female Christian 56 88 88 99 65
Maulid male Muslim 87 56 75 67 56 87
Hanifer female Muslim 56 34 67 87 78 44
hardeep female Hindu 67 36 43 34 35
janet female Christian 45 89 87 4 67
Andrew male Christian 45 68 88 23 87
Habib female Muslim 67 76 32 87 56
vijay male Hindu 75 56 87 28 45 78
Jack male Christian 88 67 34 77 67 32
H
hi Svetlana,
now I'm working with Windows 7
here are the data in A1:E6, now im using array to count them by each character:
89 89 89 89 9
35 35 35 35 47
39 39 39 39 48
356 356 356 356 46
3589 3589 3589 3589 3459
1 1 1 1 12
{=SUM(LEN(A$1:E$6)-LEN(SUBSTITUTE(A$1:E$6;B9;"")))/LEN(B9)}
and the reault will be:
1 2
5 6
9 8
14 9
0 0
17 3
13 5
4 4
5 1
1 7
is it possibble to sort them from smallest to biggest instead in one formula? so the result will be..
0 0
1 2
1 7
4 4
5 6
5 1
9 8
13 5
14 9
17 3
thank you for your help..
Hello Raja,
The value in cell B9 is missing. If you can send us the complete workbook with your data, we'll look into the issue. However, there is a very slim chance that your data can be sorted using a formula.
Oke I will..working on it..thanks
hi Mam,
I sent you an email, hope you can help me with my problem.
Thanks,
Angelo
Hi Angelo,
Our support team responded to you, hopefully the response was helpful.
I am working on a spreadsheet with a basic countif formula thats working well for me. When I add a filter to the worksheet in column A and filter by one item (e.g Apple) the formula does not autmoatically updated. Please help.
Hi, I am having a country, site and subject number column and visit 1 to visit 62 columns with the date information within an excel spread sheet. I would like to calculate
- count of subjects per country per site per visit within the specific date range
- count of subjects per country per visit within the specific date range
As I am having a huge amount of data I would prefer to avoid the manual work which will take a lot of time to find out this information. Can you please help me?
Hi!
Please send us a sample of your data at support@ablebits.com and include the resulting report you want to get, and will try to help.
Hi.
I'm trying to count the number of times a specific reason code (27 codes in total) occurs within a specified date range. I was able to use the COUNTIFS statement to identify the date range, but I can't figure out how to add the second part of the equation to the formula - that of now counting how many times each of the 27 codes occurs within the date range. The formula I'm using is:
=COUNTIFS($A$17:$A$50,">="&$R$30,$A$17:$A$50,"<"&$R$31). Any suggestions as to how I can add the next part of the statement?
Thanks for your help!
I
Hello Jane,
Just add one more Range/Criteria pair to your formula: =COUNTIFS($A$17:$A$50,">="&$R$30,$A$17:$A$50,"<"&$R$31, $B$17:$B$50,$R$32)
Where $B$17:$B$50 – the cells that contain the reason code, and $R$32 is the cell with the code you want to count.
pls av been trying to formulate a formula for my work and I need ur help. am dealing with different index nos and there various transactions whether credit or debit. I need a formula that can help me count de individual no of credit and debit for each index no and also sum the various credit and debit amount.
Hello Linda,
I am sorry, it is difficult to suggest something without seeing your data. If you can send a workbook with your sample data at support@ablebits.com, I will try to help.
I found the answer for: COUNTIF(Sursa!$G:$G,"Answered")+COUNTIF(Sursa!$G:$G,"Blind Transfered")+COUNTIF(Sursa!$G:$G,"RONA")
instead of it, the formula is:
SUM(COUNTIF(Sursa!$G:$G,{"Answered","Blind Transfered","RONA"}))
for those who will have this problem in the future.
Great combination Kolegu. Thanks a lot, really helpful
Hi,
How can I count if criteria is in a list?
For Example: instead of =COUNTIF(Sursa!$G:$G,"Answered")+COUNTIF(Sursa!$G:$G,"Blind Transfered")+COUNTIF(Sursa!$G:$G,"RONA")
to have something as countif(Sursa!$G:$G,"Answered" or "Blind Transfered" or "RONA")
Thank you.
First Column 2nd column
List =LST what is the formula of getting a shortcut e.g i type
Cable List next column will automatic appear LST and so and so fort
Wire Thanks awaiting your prompt response.
OR
A1 is 6:00 AM
B1 is 14:00 PM
A2 is 15:00 PM
B2 =A2 (This should not appear in the range of A1-B1) it will leave blank
Hi Svetlana,
this is my problem
A1 B1 C1
6:00 - 14:00 14:00 - 20:00 20:00 - 6:00
A2 = B2 = C2 =
A3 = B3 = C3 =
for example, i have a value in different excel sheet.
if i enter 8:00 am there, it will be automatically appear in A2.
B2 and C2 will be blank.
but if i put the value 15:00 pm, it will be automatically appear in B2. A2 and C2 will be blank.
Hope you can help me with this.. Thanks
How do I use countif to meet the same criteria but across multiple ranges in multiple worksheets?
I am trying to tally the number of calls by day-of-the-week each person has from a table that looks like this but for the entire year:
JANUARY Sa Su Mo Tu We Th Fr
James call
John call call
Mike call
Jane call call
FEBRUARY Sa Su Mo Tu We Th Fr
James call call
John call call
Mike call
Jane call call
MARCH Sa Su Mo Tu We Th Fr
James call
John call call
Mike call call
Jane call
I have a table at the top of the spreadsheet that shows the results and looks like this:
Sa call Su call Mo call etc
James ?? ?? ??
John ?? ?? ??
Mike ?? ?? ??
Jane ?? ?? ??
What formula do I use that references the names in the left column with the rows to give me the ?? tallies based on day of the week when they occur? Thanks.
Hi,
Help needed on the formula for getting the desired count as shown in the table.
Need to get count as 1 when either or both the parts are 100% completed.
Name Part Status Desired Count
A LHS 100.0% 1
A RHS 100.0%
B LHS 100.0% 1
B RHS
C LHS 1
C RHS 100.0%
D LHS 0
D RHS
Thanks a lot in advance.
Hi
I have two columns named as Reportable (with Yes or No) and another column named Responsible_Officer.
I am trying to do a number count for each yes per each individual responsible officer.