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 have three different team members. A,b,c. Each one has a due date. I need a formula that counts if today date falls within 7 days of due date & by employee.
Please
Hello Chris!
I think you may find this instruction helpful: Get number of days between today and another date.
I can offer this formula:
=IF(AND(A1="A",(TODAY()-B1)<7),"Yes","No")
If this is not what you wanted, please describe the problem in more detail.
Hi, I have a question.
How to use Countif to calculate how many cases are closed each day in October 2024 in "Sheet 1"? Sheet 1's column A lists dates in sequencial order (such as 10/1/2024, 10/2/2024,......10/30/2024, etc). Souce of the data are listed in another workbook: column "Closed Date" lists the closed date if a case is closed (e.g. 10/1/2024 if it is closed on 10/1/2024); if it is not completed, "closed date" column shows blank.
Can the source of data workbook be closed or has to be opened all the time in order to make the formula works?
Hello Eva!
When you create a link in Excel to a different book, the other book does not have to be open. If the source book is closed, then you will need to add the full path to the external link.
Read more: Excel reference to another workbook.
Hello,
I am looking for a formula with several conditions too.
I want to count a range of numbers but from only if they appear from a specially named column. To work out the average of the columns I use the average if, but now I want to count the number in range of cells.
So the names of the columns could be T1, T2 or T3 (from cells H5:S50) but I also want to just count who of those in T1 columns get <2.9 or T2 who get <2.9.
Is this possible I have been reading but every time I try I get an error message.
Thanks in advance
Hello Lucy!
You can calculate the average by condition using AVERAGEIFS function. I recommend reading this guide: Excel AVERAGEIFS function with multiple criteria. I'm not quite sure what the phrase means: "names of the columns could be T1, T2 or T3". Therefore, I can't recommend the formula to you. Provide additional information to understand what you need.
How to create a formula to prevent duplicate input in the following example 0038324929A123B:90 and
0038324929A123B:85 the two data are duplicates, because: 90 and: 85 are ignored, so only 15 items will be prevented from being duplicated (0038324929A123B)
I created a formula like this =COUNTIF(LEFT(A1:A100,15),LEFT(A1,15)>1 but the error did not work
Hello, try this SUMPRODUCT formula to count the number of values that have the same first 15 characters:
=SUMPRODUCT(--(LEFT(A1:A100,15) = LEFT(A1,15))) < 2
Hi there,
I've been searching around trying to find an answer that matches what I'm looking for but can't seem to either find someone who has the answer (unlikely) or the right search terms to bring up what I need (most likely!).
I have a spreadsheet with a large list of students names. I am trying to add up the number of trips and extra-curricular clubs that each student has attended this term. I have a total number of trips for each student.
For the extra-curricular clubs, their name has been added a number of times, for each time they have attended various clubs. I think I'm looking to do a 'countifs' formula to add up the number of times a student's name appears on the list however I can't find a formula that will enable me to count how many times each student's name in the register is used in the clubs register (exact match). I don't want to have to create an individual formula for each student as there is over 1000 students and I'd like to do this every term (where new students arrive/old students leave etc).
Please could you help me get to the bottom of this? I'm sure I must be missing something very simple!
Thank you
Hello Chris!
Your task cannot be done with a single formula. You can create a list of student names using UNIQUE formula. Then, for each name, you can do a count of how many times it is in your table. These instructions are useful for that: How to use Excel COUNTIFS and COUNTIF with multiple criteria. Create a formula for one student and copy it down the column as far as necessary.
You can get a list of students and calculate the number without using formulas. To do this, use a pivot table as described in these instructions: How to make and use Pivot Table in Excel.
Hi , i want to count occurence of a criteria in a range but match only the first 6 characters in that range eg: my range is supoose - "
OFF:
01 Apr 00:00 To 01 Apr 23:59
" "
OFF:
02 Apr 00:00 To 02 Apr 23:59
" "
OFF:
03 Apr 00:00 To 03 Apr 23:59
" "
OFF:
04 Apr 00:00 To 04 Apr 23:59
" "
RCPF
05 Apr 10:0005 Apr 13:30
, RCPM
05 Apr 10:0005 Apr 13:30
, B787 DEL AI99/AI99 DEL
05 Apr 20:45 To 06 Apr 04:55
"
now i want to count occurence of off in this range but each cell has more characters than just OFF:
Hi! To determine a partial match of text strings, use these instructions: How to find substring in Excel.
We have a special tutorial that can help to solve your problem: Excel formula to count cells with certain text (exact and partial match). For example:
=COUNTIF(A2:A10, "*off*")
Hello!
I have an excel file that I need to count a specific range of minutes.
1. Greater than 0:46
2. Less than 0:45
3. 0:00
Is there a formula that I can count less than 0:45 but won't count the 0:00 as well?
Thank you!
Hi! To calculate values for multiple conditions, use COUNTIFS formula. Look for the example formulas here: How to use Excel COUNTIFS and COUNTIF with multiple criteria. To set the time value, you can use TIME function.
Based on your information, the formula might look something like this:
=COUNTIFS(A1:A10,"<"&TIME(0,45,0),A1:A10,"<>0")
Hi, for the countif within a date range, is it possible instead of having the actual dates in the formula that the formula could reference a cell that contains a date?
I have =COUNTIF(U8:U17,">=D19")-COUNTIF(U8:U17,"<=F19") where my start date is in D and end date is in column E but it is coming up as zero but there are 10 dates in column U that actually meet this criteria.
Hi! In the COUNTIF formula, you can use a reference to a date cell.
You can see an example of this in the section above: Using Excel COUNTIF function with dates.
=COUNTIF(U8:U17,">="&D19)
Sorry my mistake, it's column F that has the end dates, but what I have isn't working. I tried:
=COUNTIF(U8:U17,"="&F19)
and
=COUNTIF(U8:U17,">="&D19)-COUNTIF(U8:U17,">="&F19) (flip-flopped the signs)
It still comes up at zero and not 10. Just wondered what I am missing.
It did it again. Basically look at the second formula and the first i tried was with the > and < signs reversed.
Hi! The first condition is greater than the start date. The second condition is less than the end date.
=COUNTIF(U8:U17,">="&D19)-COUNTIF(U8:U17,"<="&F19)
You can also use the COUNTIFS formula to count by two conditions. Read more: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria.
=COUNTIFS(U8:U17,">="&D19,U8:U17,"<="&F19)
Ah Countifs. Thank you! I tested that and that is doing exactly what I need. Thank you!
=COUNTIF(U8:U17,"="&F19)
Sorry it cut that off of my first reply
formula for date (sample June 1, 2024 to June 7, 2024) counting the criteria of murder data
=COUNTIFS('8 FOCUS CRIME RESPONSE'!B2:B,">="&B2,'8 FOCUS CRIME RESPONSE'!B2:B"<="&C2, '8 FOCUS CRIME RESPONSE'!J2:J,A3)
Hi! There is no comma in the formula. But I can't check its work because I don't have your data. If there is some other problem, please describe it in detail.
=COUNTIFS('8 FOCUS CRIME RESPONSE'!B2:B,">="&B2,'8 FOCUS CRIME RESPONSE'!B2:B,"<="&C2, '8 FOCUS CRIME RESPONSE'!J2:J,A3)
Hi Alexander, I have been racking my brain trying to come up with this formula. Can you please help?.
A1 (course name)
A2 (Experience that exempt from course)
H1 (course completed expiry date)
H2 (experience completed expiry date).
I have tried =COUNTIF(H1:H2,">="&TODAY()) however this gives me the number 2.
Is it possible to only count the value with the most/max date giving me a 1, or a 0 for a date or no date that is less then today.
I have also tried =COUNT(MAX(H1,H2)). however this gives me an answer of 1 even if H1, H2 are blank.
Hi! Based on your description, it is hard to completely understand your task. I, too, have been racking my brain trying to figure out what it is you want to do. However, I’ll try to guess and offer you the following IF formula:
=IF(H1:H2>=TODAY(),1,0)
If this is not what you wanted, please describe the problem in more detail.
Thanks Alexander, Almost but not quite there, as this return a #value! error. It doesn't like the range of 2 cells.
When I try this formula with 1 cell "
=IF(H1>=TODAY(),1,0)"
in the range it works perfectly, giving me a 1 or 0.
Is there a way to get the same result with 2 cells in the range.
In my work sheet I am joining multiple COUNTIF's to joint specific cells that are not in a specific range e.g. = COUNTIF(H46,">"&TODAY()) + COUNTIF(H35,">"&TODAY()) + COUNTIF(H62,"<"&TODAY()) etc. . Each cell points to a course proficiency date, So I need each Cell to give a value of 1 if the proficiency is in date or 0 if it is not. Except for 1 particular course.
The problem with this one particular course, there are two cells that cover this course , one cell carries the proficiency date (H1) which is generated using a VLOOPUP, the other cell date (H2) manually imputed covers a waiver if there is no proficiency date. The 1s and 0s are then added to give a maximin of 8, which tells me if the person has completed all proficiencies.
Hi! The formula I gave you works for me without errors. I don't know what data you are using. Try the formula:
=IF(OR(H1>=TODAY(),H2>=TODAY()),1,0)
Read more: Excel IF OR statement with formula examples.
Brilliant, thank you so much, your awesome
Hi,
I've tried all the formulas in Using Excel COUNTIF function with dates but I still don't get the right answers... What I wanted to do was countif the total sold if that item reached its aged month and leave "-" if not. For example the delivery date was 05/26/2022 and the date today is 05/02/2024 so aged month is 23. There's 3, 12, 24, & over 24 months cell header. So there should be data on 3&12 only since the certain product has not reached its 24th month.
formula used: =IF(LEFT($A140,5)="Grand",R$1,
IF(RIGHT($B140,5)="Total",SUMIF($H:$H,CONCATENATE($A140,"-",$B140),R:R),
IF(RIGHT($A140,5)="Total",SUMIF($A:$A,LEFT($I140,FIND("Total",$A140)-2),R:R)/2,
COUNTIFS(db_saleability_W!$P:$P,$C140,db_saleability_W!$U:$U,">="&($I140-DAY($I140)),db_saleability_W!$U:$U,"<="&($I140+R$4*30),db_saleability_W!$P:$P,$R3,db_saleability_W!$U:$U,"<="&DATEDIF(I140,TODAY(),"M")))))
INDEX:
db_saleability_W!$P:$P - STK# (data source)
C140 - STK # (report sheet)
db_saleability_W!$U:$U -DATE SOLD (data source)
I140 - 1ST DELIVERY DATE
Hi! It is very difficult to understand a formula that contains unique references to your data, which I don't have. Also for that reason, I can't check it out. To understand what you want to do, give an example of the source data and the expected result.
Hi! Thanks for replying... Here are the details: (hope it helps tho)
EXPECTED RESULT:
CELL I J K L M N O P Q R S T
First Date of Delivery 0 3 6 9 12 15 18 21 24 OVER 24 AGING
05/26/2022 58 77 124 157 162 223 235 247 - - 23
07/27/2020 - - - 1 4 5 12 18 26 41 45
Hence, there's no data shown in "24" cos it hasn't reached its 24th month. All formulas were correct the only problem is that it should not count or show data if it hasn't reached its month yet.
Hi! Unfortunately, this information is not enough to understand how you want to change the formula. I don't really understand what "if it hasn't reached its month yet" means.
Hi!
Great article.
I am looking to use the Countifs to satisfy specific criteria. If the following word is mentioned example: "SEA" and has Yes in the ajadcent column.
The issue I am running into is that there are other points in a cell, for example it will say SEA AIR COD. I want the formula to still pick it up if there are other words in the cell too.
Any solutions?
Hi! You can see the solution to your problem in this section of the article above: COUNTIF formulas with wildcard characters (partial match). Read it carefully.
Figured it out! Needed to use * * around the term!
Hello,
I've been struggling with creating a formula for a specific function I've been trying to achieve with a workbook I'm creating. I keep running into a result that doesn't make sense to me that I thought my formula should be able to perform for my required task. Admittedly, I am self teaching myself excel as I go and undoubtedly I'm running into barriers that may be relatively simple fixes as I learn more. Your website has been a saving grace for a lot of those questions and mis-understandings I've had so far, as your step by step layouts have been very informative.
Onto the problem I'm running into...
I work for a security company that (among the more expected "security" duties) keeps shift logs in the form of text entries for activities or tasks where time or manpower is spent. I've been attempting to create a better workbook for tracking types of incidents and/or tasks required on the property by various departments that occur for both the client and my company's statistical analysis. As we are required to do regular reports on occurrences. So far this data retrieval has been mostly done by hand. I've been able to create a workbook that I think is on the right path for automating a portion of this by pulling counts of occurrences from the log entries, but I've been running into an issue where it can only search for 1 key word I specify for each type of entry.
What I would like to create is a formula that I can have multiple key words that are being searched and return a value of true or false to be counted as having occurred only once.
As an example of a log entry that won't be specific enough to have concerns of confidentiality:
"Call from tenant: [store name] requesting garbage and recycling pickup. Maintenance informed."
My current search word for this example is "pickup" as it's universal for that type of request.
The formula I have been using that is functional, but limited to 1 key word is.
=SUM(COUNTIF(Shift_Log[Shift Log Entries],"*"&'Search Values'!B9&"*"))
This is working, but as I mentioned. Is only able to retrieve a count for a single search word I have placed into cell B9.
I have a worksheet set up to house tables/ranges of cells so I can expand to have multiple search words that count as a single entry into my COUNTIF function. My desired list of key words for this example are: "garbage", "recycling", "pallet", "pickup".
The issue I've been running into and my limited learn-as-I-go knowledge has been unable to figure out where I'm going wrong with my formula construction is. If I modify that formula to what I thought should work to search for multiple values and still get a count of 1 result. I get an incorrect result in one of 2 ways depending on how I try and craft the formula.
If I use the OR function, a value of 0 gets returned when my log entries clearly should be have multiple values counted as having occurred spread out in multiple cells through the entire log.
The formula is:
=SUM(COUNTIF(Shift_Log[Shift Log Entries],OR("*"&Maintenance_Requests[Maintenance Requests]&"*")))
The other incorrect result I get is if I do not use OR, I get a formula that works, but it is treating each instance of my 4 search words in a single log entry cell as a count. Which is giving me inflated counts. I don't want to know how many of those 4 words were used, I want the formula to return if ANY of the 4 WERE used in a single cell, a count of 1 (or "true") is present and counted.
The formula that returns this result is:
=SUM(COUNTIF(Shift_Log[Shift Log Entries],"*"&Maintenance_Requests[Maintenance Requests]&"*"))
I have been testing out multiple functions as I've been trying to find if it's just a matter of me using or not understanding the function properly. Or I'm constructing the formula in a way that Excel is unable to understand what I'm hoping to get as a result. (Which has occurred multiple times as I learn on various workbooks, where I have to dig deeper to understand how to craft the formula properly).
I've tested out the SWITCH function, OR, AND, OR with a nested AND, COUNT with a nested IF instead of using COUNTIF. and I have also tried without using a table or range of cells and typed individual key words into the formula in case that would make a difference (Which it shouldn't but.. still learning and wanted to make sure it wasn't a simple error I was making).
I've tried to explain this as thoroughly as I could so you can understand the result I am hoping to achieve, and after multiple days of searching and trouble shooting in between other projects and workbooks, I'm at a point where I need someone with more experience to possibly help trouble shoot where I'm hitting a wall and just not seeing where the error is hiding.
If you have any guidance or input about where the problem is, I would greatly appreciate any input that you can provide.
Thank you,
Tristan
Hello! If I understand your question correctly, you can count the number of cells containing at least one word using this SUMPRODUCT formula:
=SUMPRODUCT(--((ISNUMBER(SEARCH("pallet",A1:A15)) + ISNUMBER(SEARCH("pickup",A1:A15)) + ISNUMBER(SEARCH("garbage",A1:A15)))>0))
You can replace words with cell references that match the words.
For more information, please read: How to find substring in Excel.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
This is amazing, thank you.
I've put the formula you provided into my worksheet and it seems to be doing exactly what I needed it to. my only questions are:
How is it doing this? The SUMPRODUCT was one of the instruction guides from Ablebits I looked at but I didn't see how it could fit into the formula I was using to make it work so I moved on. From what I've so far understood. It's using SUMPRODUCT to tally values in the range compared to each of the searched values input and it's using ISNUMBER to treat each of the SEARCH values as an IF true or false by the >0 at the end of the formula to determine if the value detected is greater than 0?
Do I have that correct? I might be a little off, I'm stilly trying to parse how these functions are interacting to get this result so I understand for future projects and functions.
My second question is, because of the nature of how this formula works, am I correct in thinking that even though I can replace the words from the SEARCH with a cell that it compares against that I can edit the contents of. I can't have it search cells from an array or table that is easily expandable, because the SUMPRODUCT is looking for values to compare. I'd have to add into the formula for each search value I want it comparing against. Is this correct?
Side question, the -- immediately inside the SUMPRODUCT bracket are to nullify the array that it requires which is then replaced by the A1:A15 range for the searches, correct?
Again, thank you so much for your help with this problem. It's had me chasing my tail for a bit now.
Hello! The ISNUMBER function returns TRUE if the specified word is found in the text. The sum of the ISNUMBER functions is greater than 0, and this comparison returns TRUE if one or more words were found in the cell. Double Minus -- converts TRUE and FALSE to 1 and 0. The SUMPRODUCT function sums these numbers.
Read more: Double Unary Operator in Excel Array Formulas.
You can replace words in a formula with references to individual cells.
This is what I have come up with but it is not working I cannot find anything that closely resembles what I am trying to do and the COUNTIF explanation sheet is not giving me much guidance. I am trying to add up dollar amounts in column "O" if they are task assignments and between dates of 1/1/23 and 12/31/23.
=COUNTIFS(O2:O1077,AND(D2:D1077,"Task",E2:E1077,">=1/1/23"))-COUNTIFS(O2:O1077,AND(D2:D1077,"Task",E2:E1077,">=12/31/23"))
Hi! If I understand your task correctly, this guide may be helpful: How to use SUMIFS in Excel (AND logic). See Example 2 - Using Excel SUMIFS with dates.
Based on your information, the formula might look something like this:
=SUMIFS(O2:O1077,D2:D1077,"Task",E2:E1077,">=1/1/23",E2:E1077,"<=12/31/23")
Tried it another way with no luck.
=SUMIFS(O2:O1077,(D2:D1077,"Task",E2:E1077,">=1/1/23"))-SUMIFS(O2:O1077,(D2:D1077,"Task",E2:E1077,">=12/31/23"))
Good morning,
I have a sheet and I am trying to count if multiple criteria are met. First criteria is has to match a specific word and second criteria is has to be including and after date 1/1/24 which is contained in a different column. Here is what I have but it is not working and I have changed the sign between the two sets and no matter what I change it to it does not come out correct. Please if you could tell me what I need to fix.
=COUNTIF(D900:D1077,"Task")+COUNTIF(E900:E1077,">=1/1/24")
Hello! If you want to calculate values on two criteria, use the COUNTIFS function. Try to use the recommendations described in this article: How to use Excel COUNTIFS and COUNTIF with multiple criteria
For example:
=COUNTIFS(D900:D1077,"Task",E900:E1077,">=1/1/24")
If I may, how can I take it a step further. I want to use this function and if these criteria are met then total up the dollar amounts that would correspond to the counted items in column O
=COUNTIFS($D2:D1077,"Task",$E2:E1077,">=1/1/23")-COUNTIFS($D2:D1077,"Task",$E2:E1077,">=12/31/23")
Hi! To sum values by multiple conditions, use the SUMIFS function and these instructions: Excel SUMIFS and SUMIF with multiple criteria – formula examples.
The formula might look like this:
=SUMIFS(O2:O1077,$D2:D1077,"Task",$E2:E1077,">=1/1/23")
Thank you again.
Thank you very much for your help.
hi, good day to you. I am looking at the daily closing prices of a stock and would like to find out how many days (say) within a 20-day period that the price closed higher versus the day before. For instance, column A2:A21 are the dates and B2:B21 are the closing prices. Say if the price dropped every single day (vs. the day-before) except for One day where the price ticked higher (and subsequently continued to drop again), the Count would be ONE (1). That is, only One episode over the 20-day period, the price actually 'ticked higher'.
Could kindly guide me please how best I could approach this problem?
Thanks very much and wishing you & your loved ones bliss & health.
Sincerely.
Hi! Please try the following formula:
=SUMPRODUCT(--(B3:B21-B2:B20>0))
For more information, please visit: Excel SUMPRODUCT function with multiple criteria.
thanks much AGAIN :)
I need to determine time in position based off <6 Months, <1 year, <2 years, 3 years. Can I use a countif for that?
Jeremy 2/20/2012
Chris 7/11/2022
Lucas 4/26/2010
John 7/8/2020
Tyler 11/5/2018
Daniel 4/17/2023
Jeremy 4/29/2019
Johnny 9/9/2019
Josh 7/10/2017
David 7/18/2022
Hi! Read the section above carefully: Using Excel COUNTIF function with dates. For example, this counts dates more than 3 years from the current date:
=COUNTIF(B1:B10,"<"&(TODAY()+365*3))
cell | value | formula
A1 | |
A2 | | =A1
A3 | | =A2
A4 | 0 | =COUNTIF(A1:A3, "")
why value is not 2 what should i do if i want to output 2
Hi! Did you read the article above before asking the question? Pay attention to the following paragraph of the article above: Excel COUNTIF for blank and non-blank cells. For example, COUNTIF(A1:A3, "<>"&"")
conditional formatting rules
apply to range | =A1:A3
custom formula is | =AND(ISBLANK(A1), ISFORMULA(A1))
highlight A2 and A3
cell is blank but has a formula
need how to count including formulas
i beg you
I am getting an unexpected result when using this formula. Any suggestions? I have two columns, one with a CY date data has been received and one with a PY date info was received. I am trying to count all of the rows where the CY date is blank, but we are past the PY date this year.
=COUNTIFS('Sheet1'!F7:F75,"",'Sheet1'!G7:G75,"<"&TODAY()-"365")
=COUNTIFS('Sheet1'!F:F,"",'Sheet1'!G:G,"<"&TODAY()-365)
I have tried a few different ways now. I am coming up with 2 as an output when the data supports 7.
=COUNTIFS(J4:J471,""&B492,J4:J471,"<="&EOMONTH(B492,0))
The count is great but unable to count if in the same date.
Eg :
1/1/24
2/1/24
5/1/24
5/1/24
Total Sales in January : 3 (should be 4)
Hi, in my mind this should not be a difficult yet I am struggling. I have a worksheet with a clients and dates. I want know how many different clients visited each month. Date is column A - client is column 7 and worksheet has 1,000+ rows.
Date Name
03-01-24 Jim
05-01-24 David
10-01-24 Jim
15-01-24 Steve
17-01-24 Steve
25-01-24 Jim
31-01-24 Alex
01-02-24 Trish F
05-02-24 Barb
15-02-24 Trish D
21-02-24 Lisa
25-02-24 Jim
28-02-24 Lisa
Result for January: Jim (3) David (1) Steve (2) Alex (1)
Result for February: Trish F (1) Barb (1) Trish D (1) Lisa (2) Jim (1)
Please help! Thank you.