by Microsoft Excel provides several functions purposed for counting different kinds of cells, such as blanks or non-blanks, with number, date or text values, containing specific words or character, etc.
In this article, we will focus on the Excel COUNTIF function that is purposed for counting cells with the condition you specify. First, we will briefly cover the syntax and general usage, and then I provide a number of examples and warn about possible quirks when using this function with multiple criteria and specific types of cells.
In essence, COUNTIF formulas are identical in all Excel versions, so you can use the examples from this tutorial in Excel 365, 2021, 2019, 2016, 2013, 2010 and 2007.
COUNTIF function in Excel - syntax and usage
Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition.
For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify. Another typical use of COUNTIF in Excel is for counting cells with a specific word or starting with a particular letter(s).
The syntax of the COUNTIF function is very simple:
As you see, there are only 2 arguments, both of which are required:
- range - defines one or several cells to count. You put the range in a formula like you usually do in Excel, e.g. A1:A20.
- criteria - defines the condition that tells the function which cells to count. It can be a number, text string, cell reference or expression. For instance, you can use the criteria like these: "10", A2, ">=10", "some text".
And here is the simplest example of Excel COUNTIF function. What you see in the image below is the list of the best tennis players for the last 14 years. The formula =COUNTIF(C2:C15,"Roger Federer") counts how many times Roger Federer's name is on the list:
Note. A criterion is case insensitive, meaning that if you type "roger federer" as the criteria in the above formula, this will produce the same result.
Excel COUNTIF function examples
As you have just seen, the syntax of the COUNTIF function is very simple. However, it allows for many possible variations of the criteria, including wildcard characters, the values of other cells, and even other Excel functions. This diversity makes the COUNTIF function really powerful and fit for many tasks, as you will see in the examples that follow.
COUNTIF formula for text and numbers (exact match)
In fact, we discussed the COUNTIF function that counts text values matching a specified criterion exactly a moment ago. Let me remind you that formula for cells containing an exact string of text: =COUNTIF(C2:C15,"Roger Federer"). So, you enter:
- A range as the first parameter;
- A comma as the delimiter;
- A word or several words enclosed in quotes as the criteria.
Instead of typing text, you can use a reference to any cell containing that word or words and get absolutely the same results, e.g. =COUNTIF(C1:C9,C7).
Similarly, COUNTIF formulas work for numbers. As shown in the screenshot below, the below formula perfectly counts cells with quantity 5 in Column D:
=COUNTIF(D2:D9, 5)
In this article, you will find a few more formulas to count cells that contain any text, specific characters or only filtered cells.
COUNTIF formulas with wildcard characters (partial match)
In case your Excel data include several variations of the keyword(s) you want to count, then you can use a wildcard character to count all the cells containing a certain word, phrase or letters as part of the cell's contents.
Suppose, you have a list of tasks assigned to different persons, and you want to know the number of tasks assigned to Danny Brown. Because Danny's name is written in several different ways, we enter "*Brown*" as the search criteria =COUNTIF(D2:D10, "*Brown*").
An asterisk (*) is used to find cells with any sequence of leading and trailing characters, as illustrated in the above example. If you need to match any single character, enter a question mark (?) instead, as demonstrated below.
Tip. It is also possible to use wildcards with cell references with the help of the concatenation operator (&). For example, instead of supplying "*Brown*" directly in the formula, you can type it in some cell, say F1, and use the following formula to count cells containing "Brown": =COUNTIF(D2:D10, "*"&F1&"*")
Count cells beginning or ending with certain characters
You can use either wildcard character, asterisk (*) or question mark (?), with the criterion depending on which exactly result you want to achieve.
If you want to know the number of cells that start or end with certain text no matter how many other characters a cell contains, use these formulas:
=COUNTIF(C2:C10,"Mr*") - count cells that begin with "Mr".
=COUNTIF(C2:C10,"*ed") - count cells that end with the letters "ed".
The image below demonstrates the second formula in action:
If you are looking for a count of cells that start or end with certain letters and contain the exact number of characters, you use the Excel COUNTIF function with the question mark character (?) in the criteria:
=COUNTIF(D2:D9,"??own") - counts the number of cells ending with the letters "own" and having exactly 5 characters in cells D2 through D9, including spaces.
=COUNTIF(D2:D9,"Mr??????") - counts the number of cells starting with the letters "Mr" and having exactly 8 characters in cells D2 through D9, including spaces.
Tip. To find the number of cells containing an actual question mark or asterisk, type a tilde (~) before the ? or * character in the formula. For example, =COUNTIF(D2:D9,"*~?*") will count all cells containing the question mark in the range D2:D9.
Excel COUNTIF for blank and non-blank cells
These formula examples demonstrate how you can use the COUNTIF function in Excel to count the number of empty or non-empty cells in a specified range.
COUNTIF not blank
In some Excel COUNTIF tutorials and other online resources, you may come across formulas for counting non-blank cells in Excel similar to this one:
=COUNTIF(A1:A10,"*")
But the fact is, the above formula counts only cells containing any text values including empty strings, meaning that cells with dates and numbers will be treated as blank cells and not included in the count!
If you need a universal COUNTIF formula for counting all non-blank cells in a specified range, here you go:
Or
This formula works correctly with all value types - text, dates and numbers - as you can see in the screenshot below.
COUNTIF blank
If you want the opposite, i.e. count blank cells in a certain range, you should adhere to the same approach - use a formula with a wildcard character for text values and with the "" criteria to count all empty cells.
Formula to count cells not containing any text:
Since an asterisk (*) matches any sequence of text characters, the formula counts cells not equal to *, i.e. not containing any text in the specified range.
Universal COUNTIF formula for blanks (all value types):
The above formula correctly handles numbers, dates and text values. For example, here's how you can get the number of empty cells in the range C2:C11:
=COUNTIF(C2:C11,"")
Please be aware that Microsoft Excel has another function for counting blank cells, COUNTBLANK. For instance, the following formulas will produce exactly the same results as the COUNTIF formulas you see in the screenshot above:
Count blanks:
=COUNTBLANK(C2:C11)
Count non-blanks:
=ROWS(C2:C11)*COLUMNS(C2:C11)-COUNTBLANK(C2:C11)
Also, please keep in mind that both COUNTIF and COUNTBLANK count cells with empty strings that only look blank. If you do not want to treat such cells as blanks, use "=" for criteria. For example:
=COUNTIF(C2:C11,"=")
For more information about counting blanks and not blanks in Excel, please see:
COUNTIF greater than, less than or equal to
To count cells with values greater than, less than or equal to the number you specify, you simply add a corresponding operator to the criteria, as shown in the table below.
Please pay attention that in COUNTIF formulas, an operator with a number are always enclosed in quotes.
| Criteria | Formula Example | Description |
|---|---|---|
| Count if greater than | =COUNTIF(A2:A10,">5") | Count cells where value is greater than 5. |
| Count if less than | =COUNTIF(A2:A10,"<5") | Count cells with values less than 5. |
| Count if equal to | =COUNTIF(A2:A10,"=5") | Count cells where value is equal to 5. |
| Count if not equal to | =COUNTIF(A2:A10,"<>5") | Count cells where value is not equal to 5. |
| Count if greater than or equal to | =COUNTIF(C2:C8,">=5") | Count cells where value is greater than or equal to 5. |
| Count if less than or equal to | =COUNTIF(C2:C8,"<=5") | Count cells where value is less than or equal to 5. |
You can also use all of the above formulas to count cells based on another cell value, you will just need to replace the number in the criteria with a cell reference.
Note. In case of a cell reference, you have to enclose the operator in quotes and add an ampersand (&) before the cell reference. For example, to count cells in the range D2:D9 with values greater than a value in cell D3, you use this formula =COUNTIF(D2:D9,">"&D3):
If you want to count cells that contain an actual operator as part of the cell's contents, i.e. the characters ">", "<" or "=", then use a wildcard character with the operator in the criteria. Such criteria will be treated as a text string rather than a numeric expression. For example, the formula =COUNTIF(D2:D9,"*>5*") will count all cells in the range D2:D9 with contents like this "Delivery >5 days" or ">5 available".
Using Excel COUNTIF function with dates
If you want to count cells with dates that are greater than, less than or equal to the date you specify or date in another cell, you proceed in the already familiar way using formulas similar to the ones we discussed a moment ago. All of the above formulas work for dates as well as for numbers. Let me give you just a few examples:
| Criteria | Formula Example | Description |
|---|---|---|
| Count dates equal to the specified date. | =COUNTIF(B2:B10,"6/1/2014") | Counts the number of cells in the range B2:B10 with the date 1-Jun-2014. |
| Count dates greater than or equal to another date. | =COUNTIF(B2:B10,">=6/1/2014") | Count the number of cells in the range B2:B10 with a date greater than or equal to 6/1/2014. |
| Count dates greater than or equal to a date in another cell, minus x days. | =COUNTIF(B2:B10,">="&B2-"7") | Count the number of cells in the range B2:B10 with a date greater than or equal to the date in B2 minus 7 days. |
Apart from these common usages, you can utilize the COUNTIF function in conjunction with specific Excel Date and Time functions such as TODAY() to count cells based on the current date.
| Criteria | Formula Example |
|---|---|
| Count dates equal to the current date. | =COUNTIF(A2:A10,TODAY()) |
| Count dates prior to the current date, i.e. less than today. | =COUNTIF(A2:A10,"<"&TODAY()) |
| Count dates after the current date, i.e. greater than today. | =COUNTIF(A2:A10,">"&TODAY()) |
| Count dates that are due in a week. | =COUNTIF(A2:A10,"="&TODAY()+7) |
| Count dates in a specific date range. | =COUNTIF(B2:B10, ">=6/1/2014")-COUNTIF(B2:B10, ">6/7/2014") |
Here is an example of using such formulas on real data (at the moment of writing today was 25-Jun-2014):
Excel COUNTIF with multiple criteria
In fact, Excel COUNTIF function is not exactly designed to count cells with multiple criteria. In most cases, you'd use its plural counterpart, the COUNTIFS function to count cells that match two or more criteria (AND logic). However, some tasks can be solved by combining two or more COUNTIF functions in one formula.
Count values between two numbers
One of the most common applications of Excel COUNTIF function with 2 criteria is counting numbers within a specific range, i.e. less than X but greater than Y. For example, you can use the following formula to count cells in the range B2:B9 where a value is greater than 5 and less than 15.
=COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15")
How this formula works:
Here, we use two separate COUNTIF functions - the first one finds out how many values are greater than 5 and the other one gets a count of values greater than or equal to 15. Then, you subtract the latter from the former and get the desired result.
Count cells with multiple OR criteria
In situations when you want to get several different items in a range, add 2 or more COUNTIF functions together. Supposing, you have a shopping list and you want to find out how many soft drinks are included. To have it done, use a formula similar to this:
=COUNTIF(B2:B13,"Lemonade")+COUNTIF(B2:B13,"*juice")
Please pay attention that we've included the wildcard character (*) in the second criterion, it is used to count all kinds of juice on the list.
In the same manner, you can write a COUNTIF formula with several conditions. Here is an example of the COUNTIF formula with multiple OR conditions that counts lemonade, juice and ice cream:
=COUNTIF(B2:B13,"Lemonade") + COUNTIF(B2:B13,"*juice") + COUNTIF(B2:B13,"Ice cream")
For other ways to count cells with OR logic, please see this tutorial: Excel COUNTIF and COUNTIFS with OR conditions.
Using COUNTIF function to find duplicates and unique values
Another possible usage of the COUNTIF function in Excel is for finding duplicates in one column, between two columns, or in a row.
Example 1. Find and count duplicates in 1 column
For example, this simple formula =COUNTIF(B2:B10,B2)>1 will spot all duplicate entries in the range B2:B10 while another function =COUNTIF(B2:B10,TRUE) will tell you how many dupes are there:
Example 2. Count duplicates between two columns
If you have two separate lists, say lists of names in columns B and C, and you want to know how many names appear in both columns, you can use Excel COUNTIF in combination with the SUMPRODUCT function to count duplicates:
=SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)>0)*(C2:C1000<>""))
We can even take a step further and count how many unique names there are in Column C, i.e. names that do NOT appear in Column B:
=SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)=0)*(C2:C1000<>""))
Tip. If you want to highlight duplicate cells or entire rows containing duplicate entries, you can create conditional formatting rules based on the COUNTIF formulas, as demonstrated in this tutorial - Excel conditional formatting formulas to highlight duplicates.
Example 3. Count duplicates and unique values in a row
If you want to count duplicates or unique values in a certain row rather than a column, use one of the below formulas. These formulas might be helpful, say, to analyze the lottery draw history.
Count duplicates in a row:
=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)>1)*(A2:I2<>""))
Count unique values in a row:
=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)=1)*(A2:I2<>""))
Excel COUNTIF - frequently asked questions and issues
I hope these examples have helped you to get a feel for the Excel COUNTIF function. If you've tried any of the above formulas on your data and were not able to get them to work or are having a problem with the formula you created, please look through the following 5 most common issues. There is a good chance that you will find the answer or a helpful tip there.
1. COUNTIF on a non-contiguous range of cells
Question: How can I use COUNTIF in Excel on a non-contiguous range or a selection of cells?
Answer: Excel COUNTIF does not work on non-adjacent ranges, nor does its syntax allow specifying several individual cells as the first parameter. Instead, you can use a combination of several COUNTIF functions:
Wrong: =COUNTIF(A2,B3,C4,">0")
Right: =COUNTIF(A2,">0") + COUNTIF(B3,">0") + COUNTIF(C4,">0")
An alternative way is using the INDIRECT function to create an array of ranges. For example, both of the below formulas produce the same result you see in the screenshot:
=SUM(COUNTIF(INDIRECT({"B2:B8","D2:C8"}),"=0"))
=COUNTIF($B2:$B8,0) + COUNTIF($C2:$C8,0)
2. Ampersand and quotes in COUNTIF formulas
Question: When do I need to use an ampersand in a COUNTIF formula?
Answer: It is probably the trickiest part of the COUNTIF function, which I personally find very confusing. Though if you give it some thought, you'll see the reasoning behind it - an ampersand and quotes are needed to construct a text string for the argument. So, you can adhere to these rules:
If you use a number or a cell reference in the exact match criteria, you need neither ampersand nor quotes. For example:
=COUNTIF(A1:A10,10)
or
=COUNTIF(A1:A10,C1)
If your criteria includes text, wildcard character or logical operator with a number, enclose it in quotes. For example:
=COUNTIF(A2:A10,"lemons")
or
=COUNTIF(A2:A10,"*") or =COUNTIF(A2:A10,">5")
In case your criteria is an expression with a cell reference or another Excel function, you have to use the quotes ("") to start a text string and ampersand (&) to concatenate and finish the string off. For example:
=COUNTIF(A2:A10,">"&D2)
or
=COUNTIF(A2:A10,"<="&TODAY())
If you are in doubt whether an ampersand is needed or not, try out both ways. In most cases an ampersand works just fine, e.g. both of the below formulas work equally well.
=COUNTIF(C2:C8,"<=5")
and
=COUNTIF(C2:C8,"<="&5)
3. COUNTIF for formatted (color coded) cells
Question: How do I count cells by fill or font color rather than by values?
Answer: Regrettably, the syntax of the Excel COUNTIF function does not allow using formats as the condition. The only possible way to count or sum cells based on their color is using a macro, or more precisely an Excel User-Defined function. You can find the code working for cells colored manually as well as for conditionally formatted cells in this article - How to count and sum Excel cells by fill and font color.
4. #NAME? error in the COUNTIF formula
Issue: My COUNTIF formula throws a #NAME? error. How can I get it fixed?
Answer: Most likely, you have supplied an incorrect range to the formula. Please check out point 1 above.
5. Excel COUNTIF formula not working
Issue: My COUNTIF formula is not working! What have I done wrong?
Answer: If you have written a formula which is seemingly correct but it does not work or produces a wrong result, start by checking the most obvious things such as a range, conditions, cell references, use of ampersand and quotes.
Be very careful with using spaces in a COUNTIF formula. When creating one of the formulas for this article I was on the verge of pulling my hair out because the correct formula (I knew with certainty it was right!) wouldn't work. As it turned out, the problem was in a measly space somewhere in between, argh... For instance, look at this formula:
=COUNTIF(B2:B13," Lemonade").
At first sight, there is nothing wrong about it, except for an extra space after the opening quotation mark. Microsoft Excel will swallow the formula just fine without an error message, warning or any other indication, assuming you really want to count cells containing the word 'Lemonade' and a leading space.
If you use the COUNTIF function with multiple criteria, split the formula into several pieces and verify each function individually.
And this is all for today. In the next article, we will explore several ways to count cells in Excel with multiple conditions. Hope to see you next week and thanks for reading!
1067 comments
Hello,
I want to count the number of blank cells from a specific character.
The problem is that the range is extended with one cell, each day. Can you automatically extend that range each day?
Also, eventially the specific character will appear again in later cells, then I want it to start the count of blank cell from this "new" specific character.
Example:
The letter P is in cell B2 the range is B2:G2, hence 5 blank cells. Two days after I want the range to be B2:I2 i.e. 7 blank cells.
Another three days later the the letter P appears in cell K2 (range is now B2:L2), hence it should start the count from K2 i.e. 1 blank cell.
Thank you
Hope I didn't complicate the explaination?! =)
Thank you
Hi
Never mind, I figured it out.
thank you
Hi
I want to be able:
If column A = certain text, then I want all the Values in Column B(same row) be summed together:
For example:
Profession Mark
Nurse 5
Doctor 2
Careworker 2
Doctor 6
Doctor 7
Nurse 8
Nurse 5
Nurse Doctor Careworker
(sum) (sum) (sum)
Not sure if its possible, but can you Count the Blank spaces in One Column (say Column A) if there is specific text in Column B that corresponds to the blank area. For example, I need to know how many have responded to my invitation (blanks)in column A, but have informed me they are available (Word: Available)in column B. I have tried to combine a =CountBlank with a =COUNTIF but it doesn't seem to be working for me.
Thank you!
Greetings
Making an attendance sheet
Want 5 T=Tardies to couny as a 1
Ex. 28 periods a week one has 12 Tardies which means 2 absents so 112 periods a month in average so how or which formula can help me count 5 T=Tardies as 1
Thanks in advance
I need to count the number of names in a column, but exclude any that have been struck through.
Is there any site where we can get online support for MS office (Excel) I mean through chat...
Hi Bilal,
I am sorry, I do not know any website that provides free online consultancy / support for Microsoft Office.
Trying to calculate the number of times certain words appear in a column. Some of the cells works others don't and I am using the exact same formulas for both. Even when I click in on the fx button the total is correct in the formula, but it won't replicate it to the cell. The cell just shows the formula an not the number. I have all the cells formatted to general, even the ones that are working correctly.
Hello Lance,
It's hard to pin down the cause of the problem without seeing your data. If you can send you sample workbook at support@ablebits.com, we'll try to figure it out.
I tried using the countif(range,""&"") to count the cells within a range in my sheet that have something written in them and it keeps counting all of the cells selected.
If I tried counting all the cells that are blank instead and write countif(range,""&"*") it brings me to 0.
Any idea of why it wont count my cells that have data?
Hi Samantha,
Please try using the formulas described in the Excel COUNTIF for blank and non-blank cells section. They should work.
Hi Svetlana,
Unfortunately when using both functions to count non blank cells it still counts all the cells for me.
The way I input the function is : =COUNTIF(E21:E27,""&"")
Is that a correct way?
I only have test1 in 1 of the cells written yet that function comes out as 7.
Any ideas of what might be going on in the excel?
Hi Samantha,
This is the correct formula to count non-blank cells.
=COUNTIF(E21:E27,"<>"&"")
Does it work for you?
Hi,
I also tryed this formula but it counts cells with "".
Try this to exclude blanks
=COUNTIF(E21:E27,">"&" ")
Space in double quotes
and this to only include blanks
=COUNTIF(E21:E27,"<"&" ")
Thank you Mike M, I've been trying to countifs with a blank string "". Doing what you said to exclude blanks works like a charm.
Hi Svetlana!, I hope you can help me...
I am making a sample-test exam where the correct answer will be hidden in column C and column B will be where the person who is taking the exam will place his/her answer. I need a formula to tell me how many correct answers were given. For example:
-X1- = test takers' answer
-X2- = correct answer
-X1- -X2-
A B
C C
A D
D D
B B
I need a formula that will tell me that the test taker answered 3 questions correctly and then divide that value by the number of questions, in this case 5 questions. FYI: The answers will be side-by-side, however many blank rows will separate each answer (to facilitate the question and multiple answers in column A).
Thank you,
Rod
Hi Rod!
You can use the following array formula. Change 30 to the number of your last row with answers, and remember to press Ctrl + Shift + Enter to complete the formula:
=SUM(NOT(ISBLANK(C2:C30))*(B2:B30=C2:C30))
Then you can divide the result by the number of questions.
BTW, please be aware that one can easily see the content of a hidden column. More details are in this blog post:
https://www.ablebits.com/office-addins-blog/excel-unhide-columns/#disable-unhide-columns
Hi Svetlana!
I tried to use this sample You provided for Rod, but it doesn't seem to work, and I don't get what's wrong...
1) I entered some values in B and C columns
2) entered formula exactly as it is in Your sample and pressed Ctrl+Shift+Enter
as a result I see that formula as a text, and no result... why is that so? What am I doing wrong?
Here is the screenshot how it looks:
http://screencast.com/t/QRsaafuSvzw
Hope, You can help!
P.S. This is a really nice and helpful blog post!
OK, I gound what's wrong... i didn't know that brackets {} are added automatically and I don't have to enter them manually :)
Hi Uldis,
It's great that you sorted this out! Those brackets might be confusing indeed, I added them to show that is an array formula. But may be I'd better remove them not to confuse someone else :)
I am trying to create an auto Vacation equation template. I have completed the template and it is working well except, I would like it to do something like this.
Example:
=COUNTIF(D3:J20, "BBB-V") Currently the template is counting one for each one it sees, but what I would like it to is to equate 1 with a (NUMBER) like "10" to allow for actual hours employee is being deducted from balance the employee has. So if it sees one I would like it to see it is 1=10 and display the number in the cell as 10... Have I made this clear?
Hello Dawn,
I am not sure I fully understand your task. You can try one of the following solutions:
Solution 1 – you can just multiply the result by a certain number, say 10, othe formula will be as follows:
=COUNTIF(D3:J20, "BBB-V")*10
If you want to pull the number from a certain cell, replace "10" with a cell reference.
Solution 2 – if you want to show 10 for COUNTIF=1, and for the other cases you want to do something different (e.g. multiply by 9):
=IF(COUNTIF(D3:J20, "BBB-V") = 1, 10, COUNTIF(D3:J20, "BBB-V") * 9 )
If neither solution work for you, please send a sample workbook with your data at support@ablebits.com and we will try to help.
HELP! I'm trying to create a list of email addresses, based on wireless carrier. So if one column has their wireless number, next column has carrier, I then want to combine wireless number with the appropriate text email. Where these are the extensions I want to add:
Verizon = @vtext.com
AT&T = number@txt.att.net
Sprint = number@pm.sprint.com
Tmobile = number@tmomail.net
So if someone lists
5556667777 Verizon
6667778888 Sprint
How do I create a cell that says If A6=Verizon, then 5556667777@vtext.com??
It's a list of a few hundred, so I'd rather no do manually. Thanks so much for the assistance!
Hello Virginia,
To fulfill your task, you need to create a lookup table "CarrierName = Domain" in another worksheet. Then, you will be able to pull a domain by the Carrier name using the below formula below and concatenate it with the telephone number. Here is the formula:
=IF(AND(A2<>"", B2<>"", MATCH(B2,WirelessCarriersData[Name], 0)>0), A2 & VLOOKUP(B2,WirelessCarriersData,2,FALSE),"")
Where A2 is the first cell in the Phone column not including headers, B2 is the first cell in the Carrier column, and WirelessCarriersData is the name of the lookup table.
We have created an example for you, feel free to download it using the link below:
http://www.ablebits.com/_img-blog/_comments/virginia.sorensen@capitalone.com-1.xlsx
Hi
Im also after something similar to Robert.
In column A i have a number of different status ie. "On Programme", "Rejected", "Completed" etc. and in column B i have a number of programmes ie. "Cleaning", "Customer Service", etc and i want to could how many for example On programme cleaning there are?
Ive tried and other formulas similar to this but none seem to be counting what i want.
=SUM((COUNTIF(A2:A32,"On Programme"))*OR((COUNTIF(B2:B32,"CLEANING"))))
Can you help?
Hello Lara,
The same as Robert, you should also use the COUNTIFS function rather than COUNTIF, because COUNTIFS can count cells based on multiple criteria. The formula is as follows:
=COUNTIFS($A2:$A32,"On programme",$B2:$B32,"CLEANING")
You can find the full details in this tutorial:
https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/
If you want to use the SUM function, you should enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):
{=SUM(($A2:$A22="On Programme")*($B2:$B32="CLEANING"))}
I'm after something similar to what you were asking Ruth please.
I've set up the formula to count up all the instances of "*banananas*" in column B.
I then have a list of places in column A. So say the total amount of banananas is 300 I want to know how many banananas I have for each of the individual places. So it only counts the banananas entries that are next to specific text in column A.
Thanks.
Hi Robert,
In this case, you'd better use the COUNTIFS function rather than COUNTIF. It allows you to apply several criteria to different cell ranges. You can find more information about the COUNTIFS function in this article:
https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/
As for your task, you can use the following formula:
=COUNTIFS(B:B,"*bananas*",A:A,"PlaceName")
Hi There,
I am wanting to extract some values and I thought COUNTIF might be able to do it.
I have a cell that contains some data eg: ABC-BBC-ABC-SII-BBC-ABC
I get COUNTIF to count how many times BBC appears in that cell, seems it only counts the FIRST instance of BBC.
How do I get it check the entire contents of the cell and correctly record the result eg: BBC appears twice in that cell
Thanks!
The COUNTIF function cannot count specific text or characters inside a cell, it deals with the entire contents of a cell, as if you selected the option "Match entire cell contents" when performing search in Excel.
Please use this formula instead:
=(LEN(A2)-LEN(SUBSTITUTE(A2,"BBC","")))/LEN("BBC")
Where A2 is the cell containing "ABC-BBC-ABC-SII-BBC-ABC", and "BBC" is the search text. Instead of "BBC", you can enter a cell reference with the search text, e.g. B1, sometimes it is a more convenient way to work with formulas.
Hi,
I need to combine an IF statement with a COUNTIF that will count 3 out of 5 text entry options for a single column. If the COUNTIF =0, it needs to show as a dash. This is the original formula that is working =IF(COUNTIF(B1:B10000,C1&D1)=0,"-",COUNTIF(B1:B10000,C1&D1))
I need to add another criteria to the COUNTIFs, where there are 5 possible entries in column E and I need it to count 3 of these (Allocated, Declined, Rejected). Any suggestions is gratefully appreciated.
Ruth
Hello Ruth,
Let me check if I understand the task correctly. Do you want to count values in column B only if column E has any of these values: Allocated, Declined, Rejected? Or, are you looking for something different? It will be very helpful if you can paste the values from a couple of rows in columns B-E and the result you expect a formula to return.
Hi,
I have a project list where activity is shown in a timeline. I need to count up if a column 'project stage' shows activity in any given month. I have set the countif for picking up activity but now need to make it so that it only counts that activity if column 'project stage' is a particular word.
Any idea?
BTW the above is great!
Hi Clare,
You need to use the COUNTIFS function that allows setting multiple criteria in a formula. Something similar to this:
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/30/2014", D2:D9, "SpecialWord")
Where C is the column with dates and D is the "Project Stage" column.
Please see the following article for more info:
https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/
Hi There,
I am trying to record attendance at my office and I am putting together an excel spreadsheet to store the data. I need to be able to sum up the totals of vacation days, personal days, business days, and sick days. The countif works well if I put 'V' for vacation, 'B' for business and so forth, but how can I calculate half days? Is there a way that I can set up a second Countif function so when I put in something like 'VH' in the range it counts it as 0.5 rather than one? Pretty much I am wondering if I can change the amount that the countif function counts by.
Thanks
Hi Kelly-Ann,
I think you can just multiply the result of COUNTIF for "VH" by 0.5 to count half days, e.g.:
=COUNTIF(RANGE, "V") + 0.5*COUNTIF(RANGE, "VH")
I know this thread is a couple of years old, but the "Attendance/Absence" spreadsheet is *exactly* what I've been trying to create this week for my work.
The countif formula given above would work but only for one of the Key's provided. If one has more than 2 (eg one full letter for V or B or S which needs to be counted as 1 and one which has the above with a "H" in it to be counted as 0.5), then the formula wouldn't be working for all.
The secret is to use the wildcards. A Question mark - "?" can be used in the criteria to reference a single text character. So no matter what text character is used if it is on its own, it will be counted.
This would be then written as:
=Countif(RANGE,"?") and would return a value. In effect counting any cell with ONE text character in it.
To create the half version, the formula would be:
=Countif(RANGE,"*H") It works in the same way but is now separately counting only those entries with ANY character to start with (the "*") AND which have a H by them. This last then needs to be divided by half.
The example above would work, but maybe a more elegant example would be to simply divide by 2. The difficulty is figuring out the syntax which in this case is where the brackets are. The answer is:
=(COUNTIF(RANGE,"*H")/2)+(COUNTIF(RANGE,"?"))
The first pass reads the entries with the H and then divides by 2. The entire phrase is bracketed together (usually COUNTIF doesn't have a bracket before it). Excel knows that the total its found must be divided by 2 before working with the next phrase
The second pass then does exactly the same thing but with any single text character entered but does not divide. Instead its total is then added to the previous total.
NOTE: the cells which will display the result MUST BE PREFORMATTED as a Number and with one decimal place. This will provide the halves when they are counted or Excel will automatically round up.
Hope that is useful.
I'm trying to do something similar here- vacation time used and remaining based on hire date adding in at hire date additional days. so if corey has 15 days now total and used 3 in january and 4 in march and 2.5 in may then gets 15 more in august. how would i write that up? plus i have the variant of half days as well. i understand your count if you used for her issue
Simply divide it by 2, I mean after formula type /2
Hello,
I want to formulate the following:
IF "cell value A" greater than(>) "cell value B", THEN "cell value A" = "cell value B" and therefore the result "cell value C" is equal to zero
if this is doable can it be applied to multiple "cells"?
Hi Richard,
I understand your condition but cannot figure out what result you want to achieve : ) If you want a formula to add a zero to column C if a value in cell A in the same row is greater than in cell B, you can use the following IF formula: =IF($A2>$B2,0,"") where 2 is your first row with data. Since we use a relative row reference (without the $ sign) the formula with compare values in each individual row when copied across multiple cell. If you are looking for some other result, please clarify.
I want to group or count values that start the same. In this short example YTOP.125 should be grouped together - or counted. I can work with either. So I want to be able to say "if the first, for example 8 characters are the same then...
For example
ColumnA
YTOP.125SP
HYEP.556M
YTOP.648
MNTK.593
HYEP.257
YTOP.125LO
Can you help? Is this possible?
Hi Karo,
Yes, it is possible, but you will need to add a helper column to your table. If this is acceptable, then create an additional column and copy the following formula across that column, which will extract the first 8 characters =LEFT(A2,8). After that, you can proceed in 2 ways.
Way 1 – using Subtotal:
- Sort your table by Column A or the newly created column with the above formula.
- Apply subtotal to the table (Data >Outline >Subtotal) with these settings:
At each change in : Column with the formula
Use function: Count
Add subtotal to: Column with the formula
- Click Ok.
- Then Press Number 2 at the left side of you sheet and you will get the count of codes by the first 8 characters.
For more info about using subtotals please see this article - Using Subtotals in Microsoft Excel
Way 2 – using a pivot table:
- Insert a pivot table (select your table and go to Insert > Tables > PivotTable).
- Place your pivot table onto a new sheet.
- Drag and drop the column with the formula to the Rows section and column A to the Values section.
Hi there!
How can I find the duplicates in a single column with the same name but with different format? (e.g. "Susan Doe" "Doe Susan") can I use countif to find the duplicates?
Thanks in advance
COUNTIF(A2:A7,LEFT(A2,8)&"*")