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
Hi - I'm trying to compare two worksheets to see if an email address on sheet 1 is in a range of cells in Column A in sheet 2. I've tried using vlookup and ifcount formulas but neither are working. I removed all formatting from both sheets and I'm using Excel 2013. Can you please suggest a formula or point me in the right direction? Thank you!!
Hi Meg,
Please see this article:
https://www.ablebits.com/office-addins-blog/compare-two-columns-remove-duplicates/
Alternatively, you can use the COUNTIF function. The formula will be as follows:
=IF(COUNTIF(Sheet2!$A:$A,$A2)>0,"Duplicate","Unique")
I want to count how many names appear in range C5:C11 from a list of names that I have. That list I use in drop down menus in the range C5:C11. My names are Reid, Tyrone, Chris, and Paul. I can select those names from the drop down menu. I want to count how many names from the list are in that range. I tried countifs with not sucess
Hello Omar,
You can use one of the following formulas:
=countif($C$1:$C$5," Reid") or =countif($C$1:$C$5,F2)
Where cell F2 contains the name Reid.
I'm having an issue with compiling certain data.
I'm trying to find an overall percentage of participating users for certain questionnaires they have answered. For example, I have:
=(COUNTIF('Sheet 1'!$C4:$Z4, "*CHI*")+COUNTIF('Sheet 2'!$C4:$Z4, "*CHI*")+COUNTIF...and so on for 32 sheets.
While that's fine and all for those who participated in each questionnaire: ...+COUNTIF('Sheet 32'!$C4:$Z4, "*CHI*"))/(COUNTIF('Sheet 1'!$C$1:$Z$1, "*CHI*")+COUNTIF(...up to sheet 32.
$C$1:$Z$1 being my headline, line 4 being this particular individual's responses (where "CHI" might not exist in certain sheets)
The issue I am having is that there are many individuals who couldn't complete all the questionnaires (we're talking hundreds). I don't want to have to go sheet by sheet, line by line to find and specifically eliminate which sheets they didn't complete to get the proper percentage (they didn't do sheets 12, 14, 20-22, 25 for example, and I would have to delete those COUNTIF segments from the formula, just for that one cell so those extra 6 aren't mistakingly reducing the individual's percentage).
Is there an IF or COUNTIF formula that can count an entire row only if there is a timestamp (proof they filled the questionnaire) next to the individual's name?
I have an excel spreadsheet that lists the different subjects that a student has undertaken.
Example (cells N2:N9):
Information Technology
Physics
Advanced Maths
General Maths
English Research
English Critical
English Academic
Computer Applications
I would like to insert a formula underneath this (in cell N10), to look at the subjects the student has undertaken, and return a result based on this search. I need to identify if a student has undertaken all 3 English subjects listed. I have tried multiple formulas, but cannot seem to get the right outcome, as I am searching on a range of cells (N2:N9).
Thanks
Matt
Hello Matt,
Please try the following formula.
=IF(COUNTIF(N2:N9, "*engligh*")>=3, "Pass", "")
If it is not what you are looking for, please describe the result you expect to get in more detail.
Hi there
I want my excel spreadsheet to count letters in a row but with a unique condition behind each letter i.e A=1, B=2, C=3, D=4, E=5, F=6 G=7
so if the cell in row F has b and row G has E total will 7
is there a way to do this?
jai
Hi there-
I was wondering if I can use the COUNTIF function with a very specific conditional test. I have a set of data by date where for one date in column A, there might be 4 corresponding numbers in column B. Example:
9/8/2013 43
9/8/2013 45
9/8/2013 36
Is there a way to use COUNTIF to only count the appearance of a number greater than x once per date? For the above, I would want it to only count once if the criteria were ">35".
Thanks,
Steve
Hi Steve,
Since you want to count with two conditions, you have to use COUNTIFS rather than COUNTIF:
=COUNTIFS(A:A, "9/8/2013", B:B, ">35")
Hi Lydia,
Your second comment made the task clearer.
Since you have to sum by several conditions you will have to use the COUNTIFS function rather than COUNTIF. The formula can be as follows
=COUNTIFS(A:A, F2&"*", B:B, F3, C:C, ">="&F4, C:C, "<="&F5)
Where:
F2 - the cell containing either N or M
F3 - the cell with "New Birth" etc.
F4 - the sell with the "start date", e.g. 1/4/14
F5 - the cell with the "end date", e.g. 30/6/14
Naturally, you can put the above conditions directly into the formula, but in this case you will have to re-write it for each set of conditions.
You can also use the above formula in another worksheet, by adding the worksheet's name before the ranges, like this:
=COUNTIFS([Book1.xlsx]Sheet1!$A:$A,F2, [Book1.xlsx]Sheet1!B:B,F3, [Book1.xlsx]Sheet1!C:C,">="&F4, [Book1.xlsx]Sheet1!C:C,"<="&F5)
Thank you so much Svetlana, you are a a life saver, thank you so much for your time. I am trying the first part then I will venture on to the answers going in to another worksheet. But I am guessing or hoping IO can use the paste link to do this! Thanks again
I have work book 1 which collates a range of data.
Column A - codes N2, N17, M4, M2 etc(only N and M are used, although the following numbers vary)
Column C - New Birth, 0-4 years, 5+ years
Column F - shows dates of data entry.
My question to ask is:
How many N or M (column a) have Newbirths (Column c) between the dates 1/4/14 to 30/6/14 using the dates in column F
Then how many N,M 0-4 yrs in above dates etc
There will also be 3 further date ranges, 01/07/14 to 30/09/14, 01/10/14 to 31/12/14 and finally 01/01/15 to 31/3/15.
hope this makes sense
Ideally I would like to ask additional questions for calculations possibly in a new work book, but with all data linked so it would be constantly updated. I have briefly learnt how to link 2 workbooks together.
apologies I have sent the same question
I have sheet 1 "South", contains a range of criteria.
In Column A lists codes containing N or M followed by a number ie N2, M4 etc. In Column C lists New Birth, 0-11 age, 11+.
The data above I want to both be calculated so how many New Births in N or M (any code with N "N*", are there. No mater what the number is)
with in a specific date range ie 1/4/14-30/6/14, which I know would equal QTR 1.
The question I want to ask are:
How many New Births in N (Newtown) were there in QTR 1 (1/4/14-30/6/14)
I ideally this new data I would like to be linked to a new work book, but that calculates and updates when the data changes in work book 1.
Many thanks in advance
Hi
I have Excel File with more than 1000 columns and rows and in some rows/columns, there are email addresses. I want to collect all emails only in one column. Can you please guide
Hi Svetlana,
I am using formula =IF(K5>=$A$1, "True", "False") where A1 is 01/04/2014. Now I want to add additional criteria to it as below, please can you advise.
I have A1= 01/01/2014 and B1=31/03/2014
I want C1 to calculate if D1 cell has the date that is >= A1 and <=B1 then put value True otherwise false.
Thanks in advance
Rachana
Rachana,
You don't need the COUNTIF function for this task. Just add one more IF statement to your formula:
=IF(D1>=$A$1, IF(D1<=$B$1, "True", "False"), "False")
Thank you again Svetlana!!!
Please help on another query.
I am using a formula - =COUNTIFS('6-26-52 Weeks'!$F$4:$F$52,"=Mike",'6-26-52 Weeks'!$I$4:$I$52,"=April",'6-26-52 Weeks'!$J$4:$J$52,"=True",'6-26-52 Weeks'!$Q$4:$Q$52,"=Y")
I needed the total count of Ys avilable in sheet 1 provided it matches the criteria that is - Col F to find the name e.g. Mike, Col I to check the month e.g. April, Col J to count True and then Count Y from Col Q.
For some reason it doesnt give me any formula error neither it counts Y in the mastter sheet.
Regards,
Rachana
Thank you Svetlana!
Thats quite helpful:)
Hi Svetlana,
Lets say an A column has Start date that is blank and i want to add a formula on C stating that if A1 is blank then C1 should be blank but A1 has a start date then it should calculate A1+6*7 for the date to auto populate on the 6 weeks date. Please can you advise.
From your previous reply i see we can use =IF(A1="","") for having blank cell but not sure how to further add the criteria as above. Please advise me.
Thanks in advance
Rachana
Hi Svetlana,
I have data in Col that A.
I want to know no of lines between each pipe (|)
Expected Result in B col for
first pipe is 0
Second Pipe is 6
Third Pipe is 7
Data
|
1
ZGF1213420
S ILIYAAZ
S AZEES MIAH
34-53-22
29
|
2
ZGF0316174
U SUMALATA
NARASHIMHA
MURTHI
53/1
21
|
Thanks in Advance,
Khalid
I am trying to use the count unique values function as part of a COUNTIFS equation, I need to count the amount of 'P's in one column, that have a unique number in another column. I can't get the unique equation to work though. Please help.
=COUNTIFS(E$7:R$42,">0",F$7:S$42,"P",E$7:R$42,"=1") Where did I go wrong?
Hello Zack,
You need a helper column that will show whether the number in another column is unique or not.
The formula can be as follows:
=if(countif(D:D,D2)=1,1,0)
Then you can use a value from this helper column as one of the conditions for your COUNTIFS.
As I can see in your formula, several columns are used for each range. Is this correct?
Yes that is correct, the way that we have the page formatted is largely for viewing ease.
please help me Svetlana. I have a problem about sequence of numbers that is a lot to be able to read just one in a column. eg: column A there are numbers
9,1,2,3,4,2,2,5,6,2,7,5,4,8,9,2,10, then I would like to read into 1,2,3,4,5,6,7 , 8,9,10. how the formula? thank you very much
Hello Handri,
I am sorry, it is not very clear what you want to get. If you can send the source data and the expected result to support@ablebits.com, our support team will try to help.
thanks Svetlana,it worked and saved my almost 10 mins from daily my work :).
Hi, Is there any formula to capture data from other cell if a particular cell is blank, for example A1 and B1 are the cell,if A1 is blank then data should be capture from B1.
Regards
Ashish
Hi Ashish,
Try this formula: =IF($A1="",$B1,"")
Hello Svetlana,
I'm looking to count the number of occurrences of text in column A while checking the value in column B to be >0.
Let's say if my Text repeated for 5 days a week having value higher than 0. I need 5 in front of that text.
Appreciate your help!
Syed
Hey Swetlana,
Please can you advise on below 2 quesries.
1) I have A column that shows a start date e.g. 10-1-2014 and the task needs to be completed by 6 weeks. So i want the B column to auto calculate the date of completion that is 6 weeks from the start date. Please can you advise how can formulate this one. And like also want to calculate for other target dates i.e. after 26 weeks in another column and 52 weeks in another column.
Col A(start date) Col B(6 weeks target date)
10-1-2014 ? (How to auto populate date)
1) Now I have two columns as below, please can you advise.
Column B shows - target date to be completed and the column C is showing actual date for the task completed. Now i have column D where i want value Y or N based on date of completion is greater or lesser than the target date. So if completion date is greater than target date than it should show N on Column D and similarly Y where the date is equal or lesser than the target date. Please can you advise how can set a formula for this one.
Col A Col B Col C Col D
start date 6 weeks target date Actual date Y or N ?(auto populate)
01-09-2014 ? Auto populate 20/10/2014 N - Value to auto populate
Thanks,
Rachana
Hello Rachana,
Here's the formula for column B (cell B2): =A2 + 6*7
And this one is for column D: =IF(C2<=B2, "Y", "N")
Thank you Svetlana :) It worked
Now when i use =IF(C2<=B2, "Y", "N") and along with that i want to add one more criteria that if the completion date column is blank then the Y/N column should remain blank. Please can you advise.
Thanks,
Rachana
Rachana,
Assuming that your "completion date column" is column C, here is the formula:
=IF(C2="","",IF(C2<=B2,"Y","N"))
Hey Svetlana,
Lets say an A column has Start date that is blank and i want to add a formula on C stating that if A1 is blank then C1 should be blank but A1 has a start date then it should calculate A1+6*7 for the date to auto populate on the 6 weeks date. Please can you advise.
From your previous reply i see we can use =IF(A1="","") for having blank cell but not sure how to further add the criteria as above. Please advise me.
Thanks,
Rachana
Thank you Svetlana for formula =IF(C2="","",IF(C2<=B2,"Y","N"))
It worked as desired. :)
I would need your help on below to get a formula.
I have a A column with start dates in the format e.g. A1= "01/01/2014", A2= 1/3/2014, A3= 1/2/2014. Now i want column B to auto enter the value in B1 = January, B2= March, B3= February..
Just to highligh that the Start date is not in order hence the months are not in order too. please advise which formula can be applied to have respective months value in B column based on Start date in A column.
Thanks,
Rachana
Rachana,
You can simply enter the formula =A1 in cell B1, and then copy it down to other cells of column B. This will populate column B with dates corresponding to column A. Then select column B, right click and click Format Cells > Number tab > Custom, and type mmmm in the "Type" box. mmmm is the date format that displays months only.
Hi i am trying to come up with a formula that I can find the duplicate order ID in one column and have it subtract the dates in another column so I can figure out how long it took to ship.
Hello Jeremy,
I am sorry, your task is not very clear. If you can send your sample to our support team at support@ablebits.com, we'll try to help.