The article explains Excel COUNTIF function, provides a number of examples and warns about possible quirks when using COUNTIF with multiple criteria and specific types of cells. Continue reading
Comments on: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique
by Svetlana Cheusheva, updated on
Comments page 3. Total comments: 575
Hi,
How to COUNTIF specific model (eg. CHEVY) which had expired last 2 weeks (let's say the date today is 01-Mar-21). Moreover, the result will be on another sheet. tnx.
MODEL Registration# Reg. Date Expiry Date
CHEVY 313001 03-Jan-20 03-Feb-21
TAHOE 314001 10-Jan-20 10-Jan-22
CAMRY 315001 17-Jan-20 17-Jan-22
CHEVY 313002
LIBERTY 316001 01-Feb-20 01-Feb-21
TAHOE 314002 08-Feb-20 08-Feb-21
CHEVY 313004 29-Feb-20 29-Feb-21
TAHOE 314003 15-Feb-20 15-Feb-21
CHEVY 313003
CHEVY 313005 29-Feb-20 28-Feb-21
TAHOE 314004 15-Feb-20 15-Feb-21
CHEVY 313006 29-Feb-20 25-Feb-21
Hi,
The formula below will do the trick for you:
=COUNTIFS(A1:A12,"CHEVY",D1:D12,">"&TODAY()-14,D1:D12,"<"&TODAY())
You can learn more about COUNTIFS in Excel in this article on our blog.
Hi There
I have a column containing over 1,000 entries of about 200 company names. I want to count the number of times each company name occurs. How do I do this?
I'd like to save the result in a new sheet.
Any idea? Thanks for your help.
Anna O.
Hello!
You can get a list of unique values using the UNIQUE function. You can count how many times each name occurs using the COUNTIF function.
I hope I answered your question. If something is still unclear, please feel free to ask.
Can Countif be used with a function in the criteria? My column O has dates. I want to count how many dates are in 2020. I tried something like =COUNTIF(YEAR(O8:O609),"=2020") and =COUNTIF(O8:O609,"YEAR()=2020")
Hello!
Please try the following formula:
=SUM(--(YEAR(O8:O609)=2020))
COUNTIF does not work with arrays, so cannot be used YEAR(O8:O609)
I hope I answered your question. If something is still unclear, please feel free to ask.
I have built a complicated spreadsheet in Google Sheets containing several SUMIF uses. Is there an easy way to add column next to the resulting column giving the number of cells counted in each resulting cell in this resulting column? Here is an example of a cell which is a part of a column of formuli. I want to place another column the the right of this column showing the number of cells which were counted to make the results in the cell to its left.
=SUMIF(D$10:D$110, AA23, C$10:C$110)
Thank you for your help.
Hello!
If I understand you correctly, you can use the COUNTIF function with the same arguments as in SUMIF.
=COUNTIF(D$10:D$110, AA23, C$10:C$110)
I hope my advice will help you solve your task.
Hi There,
I have a column say "A" filled with data. I need a combination of formulas (IF & COUNTIF) on column B to validate firstly that if the cell A is blank, the result is blank. Else, if there is a value, it validates all of column A for duplicates and highlights for "Duplicates", else the result is "Unique". Can you please help?
Hello!
Please use the following formula/the formula below to solve your task:
=IF(A2="","",IF(COUNTIF($A$2:$A$10,B2)>1,"Duplicate","Unique"))
I hope this will help
Below formula is working for I,J,K and L. Except if i put range then the formula breaks.
Working:
=SUM((TRIM(I153)"")*(TRIM(K153)"")*(TRIM(L153)"")*(TRIM(J153)""))
Not Working:
=SUM((TRIM(I2:I153)"")*(TRIM(K2:K153)"")*(TRIM(L2:L153)"")*(TRIM(J2:J153)""))
Hi,
I gave you another formula. These formulas cannot work.
I have a formula which seems to be working ok. My formula is to check the columns I,J,K,L cannot be a blank. I am using below. I column is text, K column is text, L column is text and J column is a list.
=COUNTIFS(I2:I153,"",K2:K153,"",L2:L153,"")+COUNTIF(J2:J153,"0,1,N/A")
if the criteria is matched then it is counted as a verified row.
Users are giving a space I OR K OR L which seems to be counting as non-blank and making it as a verified row with no data that makes sense.
Is there a way to not to count a space as valid entry or just no count if only space is provided in the column.
Many thanks in advance
Hello!
If I understand your task correctly, the following formula should work for you:
=SUM((TRIM(I2:I153)="")*(TRIM(K2:K153)="")*(TRIM(L2:L153)=""))+COUNTIF(J2:J153,"0,1,N/A")
I'm not sure if COUNTIF(J2:J153,"0,1,N/A") is written correctly. But I do not have your data and I cannot check the condition "0,1,N/A". I think it should be 3 conditions.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thank you Alexander for your reply.
I need more help. You may have understood other way around. Sorry about not to be so clear.
For example there columns I,J,K,L
Criteria 1 is: I, J, K and L cannot be empty. Must have at least one character, not just spaces allowed.
Your formula worked for 3 columns if i made a change like below.
=SUM((TRIM(I153)"")*(TRIM(K153)"")*(TRIM(L153)""))
However range is not working. Getting #value error.
The j column must have a value 0 or 1 or N/A. User will be provided an excel sheet with to select from a list 0,1,N/A.
Formula in text:
Columns I2 to I153 must have at least one character (no space) and K2 to K153 (event better if it is a date) must have at least one character and L2 to L153 (no space) must have at least one character and J2 to J153 must select a value 0,1,N/A.
Hope I am clear on my question.
Thank you again for your time helping me in this.
Hi,
The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
If data in column J is selected from a list and cannot be entered manually, then only non-blank cells can be counted.
The formula counts the number of rows that have at least one blank cell.
=SUM(--(((TRIM(I2:I153)="")+(TRIM(K2:K153)="")+(TRIM(L2:L153)="")+(TRIM(J2:J153)=""))>0))
I hope it’ll be helpful.
actually the formula is this. above question have incorrect formula.
=COUNTIFS(I2:I153,"",K2:K153,"",L2:L153,"")+COUNTIF(J2:J153,"0,1,N/A")
I have COUNTIFS(H34:AI34,"OFF" in attendance schedule so with the same range i dont want to count "AL" please give me formula where i can put multiple condition.
For Example
Range is H34:AI34
Criteria is OFF and AL which mean i dont want to count OFF and AL
Hello!
The COUNTIFS function uses several conditions. Please check out the following article on our blog, it’ll be sure to help you with your task: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria
I hope this will help.
Hi
I have a list where I need to count the number of records which meet a 2 criteria.
Application name and Critical or Non Critical
The application name is contained in a string of text the Critical/Non Critical is in a separate cell.
Column A, the status Critical or Non Critical is in Column B
Column A contains one of the following
Security Global
Security Global AS400
Security Global Link
Security Global Unix / Linux
Security Global Windows
Security Global z/OS
Column B is either
Critical
Non Critical
I have a formula to count the entries for "AS400", "Link", "Windows" etc
=COUNTIFS($A$2:$A$14,"*AS400*",$B$2:$B$14,"Critical")
but if I search for Global it brings back everything.
=COUNTIFS($A$2:$A$14,"*Global*",$B$2:$B$14,"Non Critical")
Is there an easy way to get it to count the cells which contain just "Global" and not include the "Global Windows" etc
Many thanks in advance
Hello!
If I understand your task correctly, the following formula should work for you:
=COUNTIFS($A$2:$A$14,"*Global*",$B$2:$B$14,"Non Critical",$A$2:$A$14,"<>"&"*Global Windows*")
Hope this is what you need.
Hello,
I have a report that I am trying to figure out a formula for. I am using a countifs formula that has several ranges and criteria. The report has a 1 in every cell of column A, employee name in column B, date of first day in quarantine in column C, date of last day in quarantine in column D, either a 0 or 1 in column E (0=quarantine and 1=positive test and quarantine) and the final few columns are irrelevant to the formula. The information I am looking for is: 1) total positive test/quarantines, 2) total overall quarantine, 3) total 14 day quarantine based on dates in column C and D, 4) total 10 day quarantine based on dates in column C and D. The formula I am using for the each is as follows:
1) =countif(E2:E800, "1") - correct total
2) =countifs(A2:A800, "1", C2:C800, ">="&TODAY()-"14", D2:D800, "="&TODAY()-"14", D2:D800, "="&TODAY()-"10", D2:D800, "<="&TODAY()+"10")
The current total number of employees in quarantine is 7, but the formula is giving 14 which is the total number of employees that are in quarantine based on the date in column C only rather than counting if the dates meet the criteria for both columns C and D. How do I get the formula to consider the second range and criteria when counting? I thought countif functions recognized "and" rather than "or" in the format I am using.
Hello!
I cannot validate the formula without your data, but I recommend trying the formula
=countifs(A2:A800, 1, C2:C800, ">="&(TODAY()-14), D2:D800, “=”&(TODAY()-14), D2:D800, “=”&(TODAY()-10), D2:D800, “<="&(TODAY()+10))
I hope I answered your question. If something is still unclear, please feel free to ask.
Dear All,
I want to calculate no. years month and days to calculate experience tenure and then calculate that experience with 4:1 ratio please suggest formula anyone.
Hello!
To calculate the number of months and years, I recommend using this guide: DATEDIF - calculating date difference in days, weeks, months.
I hope this will help.
Hi!
How do I calculate a count of cells, omitting only the duplicates. that is, if there are 10 entries, and 2 of them appear twice, it should return an answer of 8 (not 6 or 4)?
thank you!
Hello!
You can learn more about counting unique values in Excel in this article on our blog.
I hope I answered your question. If something is still unclear, please feel free to ask.
How do I insert a VLOOKUP as the criteria
I tried but it keeps failing
For example :-
=COUNTIF($G5:DR5,"<VLOOKUP(A5,Codes!$A:$E,4,0)")
Hello!
It is not possible to insert VLOOKUP as a less than criterion in the COUNTIFS function.
I recommend using something like this formula
=SUM(--($G5:$DR5<VLOOKUP($A$5,Codes!$A:$E,4,FALSE)))
I hope it’ll be helpful.
Thank you :)
But i did figure out to insert a VLOOKUP as a criteria and it worked
=COUNTIF($G5:DR5,"<"&VLOOKUP(A5,Codes!$A:$E,4,0))
How can one count the number of cells in column A in which the values are greater than the corresponding values in column B? What Excel command should I use? I tried sumproduct but it isn't applicable as it adds up the actual values whereas I only want to know how many cells in A have values greater than B. Thanks.
Hello!
If I understand your task correctly, the following formula should work for you:
=SUM(--(A1:A20>B1:B20))
Hope this is what you need.
I am doing a COUNTIF formula =COUNTIF($C$5:$C$19,"ben*"), but I need to copy the formula to three other cells. When I copy them, the exact same formula comes up for each line, but I need the "ben*" to change each time to a different name (cap, cat, hun). How do I get my formula to recognize that it needs to change without doing it manually?
Hello!
Instead of “ben *”, write a reference to the cell with this value in the formula. Like this:
=COUNTIF($C$5:$C$19,D1)
When you copy a formula, the link will change to a different cell.
How do I count those records where Column X is not blank AND Column Y is blank? For example, Column X is "Drafts Uploaded" and Column Y is "Shipped." I need to count those record for which drafts were uploaded (Column X is not blank) but have NOT been shipped (Column Y is blank).
Hello!
You have 2 conditions. Read in this article how to count cells with multiple criteria.
what a formula be to count a range of blank cells if filtered.
Ex. have a list, only want to count blank cells. If i only want the blank cells when i filter NY, how can i get it to only count the blank cells when i filter
Hello!
In the instructions above, read the "COUNTIF blank" section.
How do you use countif when the cells you want to count have a return value in the Dated format example below.
4 Yrs, 11 Mths & 8 Days
The above shows length of service the formula used is:
=IFERROR(IF(E6="","",DATEDIF(E6,$C$2,"y") & " Yrs, " & DATEDIF(E6,$C$2,"ym") & " Mths & " & DATEDIF(E6,$C$2,"md") & " Days "),"")
Now I want to count how many colleagues have a length of service of more that 3 yrs. Is that possible?
Thanks
Phil
Hello!
You can count the number of dates for which the difference is more than 36 months:
=SUM(--(DATEDIF($E$1:$E$10,$C$2,"m")>36))
I hope this will help, otherwise please do not hesitate to contact me anytime.
I want to count alternate column value. for example i have column A-B-C-D but i need only to count column A & C Value.Please help me to know how i can do by formula.
Hello!
You did not say by what criteria you will count. Therefore, I cannot give an exact recommendation. However, you can add the two COUNTIF functions. In the first you count by column A, in the second you count by column C. Or you can use the COUNTIFS function. Read more here.
The "COUNTIF - COUNTIF to count numbers within a range" section is clearly wrong. If you just manually count the matches on the list, there are a total of 5.
Not sure if your understanding of the function is wrong or just your visual aid, but since I can't get this to work based on your instruction I suspect it is the former.
Hello Charles!
You're right - the formula does not take into account the number 15. Thank you for your attention. I fixed everything
Hi i have a spreadsheet Songs and i need to use Simplex criteria to get the names of the row, which formula do i use?
Hello!
Please describe your problem in more detail. Write an example of the source data and the result you want to get. It’ll help me understand it better and find a solution for you. Thank you.
Hi there! I am trying to combine these formulas so that both criteria must be true for it to be counted however as my ranges are different sizes this is problematic with the countif function. Is there a way around this?
These are the two formulas which need to be combined:
=COUNTIF('Volunteer Call Handler Form APRIL'!Q:V,"XXXXXXX - recent")
AND
COUNTIF('Volunteer Call Handler Form APRIL'!G:G,"Inbound")
Hello Tilly!
You can use the COUNTIFS function. But уach additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other. Read about it here.
Hi, I want to count those items which have 3 letters + 4 numbers from below, how to count it please?
ABC2001
ABC2002
ABC2003
ABC2004
ABC2005
ABC2006
ABC2007
ABC2008
ABC20A
ABC20B
ABC20C
ABC2009
ABC2010
ABC2011
Hello,
Please try the following formula:
=SUMPRODUCT(--NOT(ISNUMBER(--LEFT(C1:C14,1))), --NOT(ISNUMBER(--LEFT(C1:C14,2))), --NOT(ISNUMBER(--LEFT(C1:C14,1))), --ISNUMBER(--RIGHT(C1:C14,1)), --ISNUMBER(--RIGHT(C1:C14,2)), --ISNUMBER(--RIGHT(C1:C14,3)), --ISNUMBER(--RIGHT(C1:C14,4)))
I hope it’ll be helpful.
Sorry, not sure what happened to what I typed above. Edited:
Hello, I am trying to count the number of cells within a range of cells that fall between two values. So my values which range between 1000 and 50000 are located in cells I2 through I21. I put a value of 5000 in N4 and 25000 in O4. I want to count how many times the values in cells I2 through I21 fall between 5000 and 25000.
When I use this formula: =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4) I get 27.
When I use =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4) I get 3.
I can count the cells and see that the answer should be 7. 7 out of 20 cells fall between 5000 and 25000.
Thank you.
Hello Adina!
See answer above
Hello, I am trying to count the number of cells within a range of cells that fall between two values. So my values which range between 1000 and 50000 are located in cells I2 through I21. I put a value of 5000 in N4 and 25000 in O4. I want to count how many times the values in cells I2 through I21 fall between 5000 and 25000.
When I use this formula: =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4)-COUNTIF(I2:I21,"<="&O4) I get 3. This is also incorrect because I can count the cells and see that the answer should be 7. 7 out of 20 cells fall between 5000 and 25000.
Thank you.
Hello Adina!
If I understand your task correctly, maybe the following formula should work for you:
=COUNTIF(I2:I21," >= "&N4)-COUNTIF(I2:I21," > "&O4)
or
=SUMPRODUCT(--(I2:I21>=N4),--(I2:I21<=O4))
If there is anything else I can help you with, please let me know.
Hello,
I am trying to count the instances where a particular string of text is found in column A, and the date in column D is within a specific range. Could anyone tell me why this isn't working?
=COUNTIFS(Datasheet!A:A,"*theme*",Datasheet!D:D,">=6/1/2019", Datasheet!D:D,"<=6/30/2019")
Thank you!
Hello Madeline!
Please try the following formula:
=COUNTIFS(A1:A40,"*theme*", D1:D40,">="&DATE(2019,6,1), D1:D40,"<="&DATE(2019,6,30))
Hope you’ll find this information helpful.
A1 AHU
A2 AHU
A3 Boiler
A4 Boiler
A5 Boiler
A6 Chiller
A7 Chiller
A8 Chiller
A9 Chiller
A10 Chiller
How to count that How much AHU,Chiller & Boiler in my sheet. I Have 1000 Data in sheet.
Hi there! If you require to know how many of each "AHU", "Boiler" and "Chiller" are in your column, then I hope you find this useful.
In cell A1, let's assume there is a "Product Type" header. In cell B2, create a "AHU" header. In cell C2, create a "Boiler" header. In cell D2, create a "Chiller" header. The headers in cells B2, C2 and D2 will be your target cell references.
Let's assume "AHU" is in cell A2, "AHU" is in cell A3, "Boiler" is in cell A4, etc.
To count the total number of "AHU" in column A, the formula is: =COUNTIF(A:A,$B$1) the result should be 2. To count the total number of "Boiler" in column A, the formula is: =COUNTIF(A:A,$C$1) - the result should be 3. To count the total number of "Chiller" in column A, the formula is: =COUNTIF(A:A,$D$1) - the result should be 5.
This is wrong:
=COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15")
It should be:
=COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,"<=15")
Please update.
Hi John,
Just test both formulas on any sample data, and you will see which one is correct :)
Thank you for being very helpful, I am looking for an answer to my question, Is there a formula to this situation?
Name Amt
AA 40
DD 45
AA 48
GG
SS 54
AA 67
GG 85
AA
AA 12
KK
AA 6
LL 15
AA
QQ 4
Count all # of "AA" if there is a value in column "Amount"
Hello Harry,
If your task is to count all the AA entrances that have any amount, please use the formula below:
=SUMPRODUCT(($A$2:$A$15="AA")*($B$2:$B$15<>""))
If this is not exactly what you need and you'd like to sum these amounts, here is the formula for you:
=SUMPRODUCT(($A$2:$A$15="AA")*($B$2:$B$15))
Hello. I think I'm on the right track and the COUNTIF function is what I need.
Say I have a table in column C with some blank cells, and I want to add an adjacent column D but omit the values in column D which are a next to a blank cell in column C. Would I use the COUNTIF or SUMIF function? the cells in column D need to be summed up. Also, can you help me out with the syntax?
Thanks
Yes you can, but you'd want to use COUNTIFS as this allows for multiple sets of criteria.
something along the lines of:
if your using numbers then
=COUNTIFS(C1:C10,">0",D1:D10,">0")
if your using text
=COUNTIFS(C1:C10,">""",D1:D10,">""")
hi there
i got a question , how can i make a formula that doesnt count 0 but show less then 0, for example in my file i got a row that show my active items in store and in other row it shows the number of negative margins from that item
i did this formula for it
=COUNTIFS(MKT!F:F,"AC",MKT!Q:Q,"<0")
but it count the "0" number for "AC", i just want lower then 0 not the zero number
you might have small decimal number e.g. 0.00001
My Formula
=COUNTIFS('Daily Activity Register'!$M:$M,"Vidhu.Khosla",'Daily Activity Register'!$B:$B,"Walk-in",'Daily Activity Register'!$AH:$AH,"N",'Daily Activity Register'!C:C,J18) isfailing When last condition i am trying range of dates.
Daily Activity Register'!C:C is dates
When i use the condition ">="J18 it says Invalid
Hi there,
I am trying to create a database and am struggling to count the number of clients between certain ages and certain admission dates. I am using defined names and my formula looks like this:
=COUNTIFS(Dyn_Gender,"F",Dyn_Age,">64",Dyn_Age,"=01/04/2019",Dyn_Admission_Date,"<=30/04/2019")
However I am not getting any results when there should be at least 1, any advice or workarounds? I am unsure which part of the formula is incorrect...
Sorry typo in formula above, refer to this:
=COUNTIFS(Dyn_Gender,"F",Dyn_Age,">64",Dyn_Age,"=01/04/2019",Dyn_Admission_Date,"<=30/04/2019")
I have a column with dates and I am trying to count how many have a date in them. I have used =countif(c2:c2885,"*") and it works lovely on everything but dates. Any suggestions would be fantastic.
Hi did you receive an answer for this question?
I have a column with a long list of dates and another column with individual dates. I wanna count how many times the specific date in the second row comes up in the first row.
EG: Countif(range,a1)
Range: long list with multiple dates
a1: cell with the specif date that we are looking for
=COUNTIF(range,""&"")
this counts also cells containing FORMULAS!
For instance if I have a formula that produces either a number or empty cell (based on some calculation) the above countif formula will count even the empty ones.
I had to use the =COUNTIF(range,""&"*") formula as I had only number or EMPTY cells. Dont's know however if also date and other data types would be outcome of calculating formula, how this would end?
as I was afraid, this is not counting cells containing strings, only numbers and may be dates?)
I have an array (say I5:Y5) with each cell holding a string (say 1-2-0, 3-0-0, 1-1-1, 0-2-1, etc). I want to count the number of cells in the array where the first number in the string is greater than the second number in the string. I have tried:
=COUNTIF(I5:Y5,(LEFT(I5:Y5,1)>(MID(I5:Y5,3,1))) with no luck and have tried various forms (such as using quotes around the >, quotes around the formula, etc.), still no luck. It seems there is no COUNTIF formula where I can count when the first number is greater than the second number in the string. Can this be done, either via editing this formula or by VBA code?
Hello, Dennis,
The formula won't work with COUNTIF. Try using SUMPRODUCT instead:
=SUMPRODUCT(--(MID(I5:Y5,1,1)>MID(I5:Y5,3,1)))
Hope this helps!
=COUNTIFS(F2:F255,"FTD - Operations")=COUNTIFS(G2:G255,">="&E258,G2:G255,"="&E258,G2:G255,"<"&EDATE(E258,1)) IT COUNT THE DATE REQUIRED
I NEED TO COMBINE KINDLY HELP ME OUT
THANKS IN ADVANCE
MY ID MAQBUL2005@GMAIL.COM
Hello, Maqbul,
Please have a look at this article on our blog to find out how to create COUNTIFS formulas with multiple criteria.
Hope you'll find this information helpful.
I am using the COUNTIF function to look at a number of date cells and count the number that are overdue. If i type the formula =COUNTIF(E2:E6,"<1/12/18") it returns the number 3 which is correct. I want the formula to use todays date automatically but substituting 1/12/18 with TODAY() returns zero. Have also tried substituting 1/12/18 with a cell (B12) but again 0 returned. Any ideas?
Add the ampersand, freund. See below
=COUNTIF(E2:E6,"<"&TODAY())
Hi,
If i want to count the number of cells in the range B2:B10 with a date greater than or equal to the date in another range (let's say Z2:Z10). How can i change the following formula:
=COUNTIF(B2:B10,">="&Z2-"7")
Thanks!
XP,
Provided all the cells are formatted for date your formula should work...if you take the 7 out of those quotes...so:
=COUNTIF(B2:B10,">="&Z2-7)
Can i use the countif function to do the following: in a range of rows,i want to count the rows and then select only those rows where the value in column B differs from the value in column A.
sample data please, Josh...better answers with better deets
I am trying to get the highest value from a list of serial number range list
LOOKUP(2,1/(COUNTIF(K3:K200,">"&K3:K200&"*")=0),right(K3:K200,12))
i am looking for the max value by searching only the rightmost 12 letter in range.
Sample data
-----------
K
000112717423 - 000112783422
000112783423 - 000112837322
000112837323 - 000112811822
000112811823 - 000112812322
000112811823 - 000112812322
...
expecting answer
----------------
000112837322
because i am using excel 2010, i cannot use the following code
=MAX(VALUE(RIGHT($K$2:$K$200,12)))
Hi Ken,
This is most easily solved if you are able to insert a separate column as an intermediate step to get the value. e.g. cell L2: =Value(Right(K2,12)). This can be hidden but you need to there are enough formulas if you add new data.
Andrew K.
there is lots of number 0 to 2000. i want to count how many numbers are there between 0 to 100. For that i use =COUNTIF(A1:A20,"<100"), then what is the formula i need to use to count 101 to 2000?
reaz, assuming that you want to include 101 and 2000 in your count, then use =COUNTIFS(A1:A20,">100",A1:A20,"101",A1:A20,"<2000").
Please note that your original formula =COUNTIF(A1:A20,"<100") will include any zeros, so your count will be 100, not 99
Since you want numbers btn 0 and 100, it implies 0 & 100 will be left out in the formula, so the best way is to use a multiple function as;
=COUNTIFS(A1:A20,">0",A1:A20,"<100")
Hi,
I'm trying to create spreadsheet that calculates student scores.
Students get graded as either a 1, 2 or 3 which goes in a column next to their name - pretty standard.
What i'm having trouble with is trying to count the number of students who got a grade 2 but who also tick another criteria, which is that they have English as an additional language (EAL). I have a column next to their name which simply has a Y in it if English is their additional language. So i'm tring to count: "How may students who have EAL got a grade 2?"
I managed to get the count for those with grade 1, and 3 but when trying to apply the same formula to the 2's, i get the error message: "ErrorFunction IF parameter 1 expects boolean values. But 'Y' is a text and cannot be coerced to a boolean."
This is the formula i used to successfully count the students with EAL that got 1's and 3's (the initial data is on a different tab called Y6):
=IF('Y6'!D2:D33,"Y")+COUNTIF('Y6'!AF2:AF33,"1")
=IF('Y6'!D2:D33,"Y")+COUNTIF('Y6'!AF2:AF33,"3")
I get the error message above when i use the same formula for the 2's
=IF('Y6'!D2:D33,"Y")+COUNTIF('Y6'!AF2:AF33,"2")
Any idea how i can calculate these totals?
Thanks!
Use COUNTIFS instead =COUNTIFS('Y6'!D2:D33,"Y",'Y6'!AF2:AF33,"1") and replace 1 with 2 or 3.
if the number is 15 digits (37AAYYCC0866A1Z) its ok less then 15 digits number is not Ok give me the formula
A Rajesh, assuming your first number is in A1, then use LEN(A1)=15. This will give you TRUE or FALSE values.
If you must have "OK"/"Not OK" then use if(LEN(A1)=15,"OK","Not OK").
Hi Ablebit team.
I am trying to count the number of cells in a column which contain a 6 digit number that starts with 5, and the third and fourth digits of that number are 60 (for example, 52601, 53607, etc.).
I have tried =COUNTIF(C:C,"5?60??") and it always calculates the answer as zero.
I have also troubleshooted with other wildcards ("5*", "5?????"), etc and every time it answers zero.
The only way I can get a real value is if I set the criteria as a full 6 digit number (for example, "516000")
Am I using the wild card incorrectly? Please help! Thank you.
Hi EY,
This is a deep problem with how Excel is storing the numbers you are searching. In using those wildcards, Excel tries to match text values, not number values, as Svetlana alluded to above. There are several fixes, none of them ideal:
1. Change the cell format from General to Text. This will cause problems if you ever try to enter a formula in those Text formatted cells.
2. Insert a new column D, with a function to convert the number to text, e.g. cell D1: =Text(C1,"#") and copy that formula for the rest of column D down to the last number you have in column C. If column C is already a formula, wrap your formula inside the Text function, e.g. cell C1: =Text(Sum(A1,B1),"#")
3. Insert a new column D and do the matching using text functions. e.g. cell D1: =AND(LEN(C1)=6,MID(C1,1,1)="5",MID(C1,3,2)="60"). This checks that length is 6, the first character is 5 and characters 3 to 4 are 60; you can then do a count of cells with a TRUE result, e.g. =COUNTIF(D:D,TRUE)
Nicely Explained!
Q)
Suppose I have a Columns which contain some text and numbers including some errors also like (#N/A,#VALUE etc). How to count only text and numbers cell not including any error in it.
Thanks in Advanced. :)
Muhammad:
I think this formula is what you're looking for:
=SUM(IF(ISERROR(A65:A76),1))
I used the range A65:A76 for my test, but you can replace this with whatever range suits you.
Hi,
I have Year in Column A (from 2005-2012), names of countries in Column B (like India, Australia, England, South Africa, etc.) and whether they "Win" or "Loss" in Column C. How do I use the Countif function to determine how many times India Won in total?
=COUNTIF(IF((B2:B1000="India")+(C2:C1000="Win")=2;1);1)
={COUNTIF(IF((B2:B1000="India")+(C2:C1000="Win")=2;1);1)}
Remember, it is an array formula. Control+Shift+Enter
Vani:
Not sure about using COUNTIF. You can use COUNTIFS like this:
=COUNTIFS(A20:A27,"India,C20:C27,"Win")
Thanks a lot. It full fill my requirement.
How can I use COUNTIF to check for duplicated entries in excel?
For example, if I have this the file below, how can CountIF show where the same STAN duplicates.
STAN Output
1234
2345
1234
5678
890
5678
6930
Edozie:
Where the STAN data is in A2:A8 the formula is:
=COUNTIF($A$2:$A$8,A2)>1 then copy it down the column.
Hey would anyone know how to use COUNTIF to compare two columns of data - dates, only counting IF the first column date is proir to the second? 30,000 data points so I cannot do it manually
Nick:
I think this is what you're looking for:
=COUNTIF(E6,">"&D6)
Where the dates are in columns E and D beginning in row 6 enter this in column F.
IFEOLUWA AKINNLO W/D ENGINEER
RAWLINGS U UWUIM BRC OPERATOR
JIMOH ISHMAEL AD W/D OPERATOR
AKANDU OKECHUKWU ELE Electrician
HENRY EYO DAVIS ELE Electrician
JOHN ETIM W/D OPERATOR
FRANKLIN IGHOROD ELE HOD
LASISI OLADIPUPO ELE D.G OP
ALEX ADEWOLE W/D OPERATOR
JIMOH RASAQ W/D OPERATOR
ADELEKE KAZEEM BRC OPERATOR
I WANT TO COUNT HOW MANY OPERATOR PRESENT DATE WISE. USING COUNTIF FUNCTION.
Dillip:
If the data is in the range A2:C27 the formula in D2 looks like:
=COUNTIF(A2:C27,"Operator")