This short tutorial explains the basics of the Excel COUNT and COUNTA functions and shows a few examples of using a count formula in Excel. You will also learn how to use the COUNTIF and COUNTIFS functions to count cells that meet one or more criteria. Continue reading
Comments on: Excel COUNT and COUNTA functions to count cells
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 115
Hi. How do I add a string of text behind a formula? I am trying to count a column and add the words behind it for the report.
Here is an example of what is not working:
=counta((A3,a11) ": Total 2019 Submissions")
Thank you.
How can I do this -
I am on Sheet2, looking for number of count of more than 80% from Sheet1. In Sheet1 there is coloum matching for "product" , "date" & "percentage". In the Sheet2 item matching from date & product. Can you please suggest me What formula I need to use to get the answer?
thanks for your help in advance
I am trying to add the total number of hours I have worked, out of a grand total (in this example 75 hours). Can I display the sum to read, for example, 48/75 to represent 48 hours of the total 75 hours needed?
Thanks for your help.
May be simple answer to this but still finding my feet in excel.
I have a report that generates a list of dates and times like this (its all in the one cell. i.e. "16/10/2018 7:22:06 AM +10:00" is all in one cell):
Last Update Date
16/10/2018 7:22:06 AM +10:00
15/10/2018 3:34:09 PM +08:00
16/10/2018 7:23:08 AM +10:00
16/10/2018 7:13:28 AM +10:00
16/10/2018 7:10:23 AM +10:00
15/10/2018 7:12:11 AM +10:00
16/10/2018 7:18:01 AM +10:00
12/10/2018 7:30:04 AM +10:00
12/10/2018 7:23:00 AM +10:00
11/10/2018 7:28:20 AM +10:00
9/10/2018 7:07:33 AM +10:00
8/10/2018 8:10:18 AM +10:00
9/10/2018 7:40:25 AM +10:00
the list is often hundreds long.
What I need to do is generate separate counts for todays date, yesterdays date up to 5 days previous. The list is updated daily but always in the same format.
The issue I'm running into is that all the data is the one cell and it wont recognise it as a date to count. I am unable to change how the report is generated :(
Thanks for any help.
found a work around. I used the text to columns function in the Data tab. seems to work. If there is a better way would still appreciate the help.
Column A contains as follows: A1"2";A2"4";A3"7";A4"6";A5"8";A6"5";A7"9";A8"3";A9"1";
and in C1 I gave "6" and in C2 I gave "9"
In D2 I give the formula that gets me the result "6" that is the position of 9 from the digit 6 is 6 counting from the row which contains the value equal to C1 i.e."6" in Column "A" and that is the beginning of the count then it counts upward and if the value equivalent to the cell C2 i.e. "9" is not fount then it continues from the bottom i.e. cell "A9" which has the value 1 in it and reaches the cell "A7" that contains the value 9 but either of the one should be excluded from the final count and state me the result i.e. "6". Which formula can I use here and what are the arguments or options in MS Excel fir this?
Expect your reply.. I am hopeless searching for this method for long time.. Please help me if you know this to do.. Thanks in advance
And also if I give 2 it should get the value of the cell which is second in upward counting of rows from the previous cell value that equals a value in a range..
I'm having an issue here:
for those names that fall under P:P may not match F4 exactly, because of an initial in the name, how would I write this to count if name is similar when looking up on different tab?
=IFERROR(COUNTIFS('Issues w-out Action Plans'!K:K,"< 30",'Issues w-out Action Plans'!P:P,'MoEVM Summary Level'!F4),"0")
what should be the formula to get unit price from daily sales sheet to another sheet.
I would very much appreciate for the help
Please help me how to make a formula to count sales item of each date that how much quantity of nokia mobiles sales in date 1 or date 2 so it will automatically count.
A B C
2 Date Description QTY
3 1 Sony 2
4 1 Nokia 4
5 1 Sony 1
6 2 Nokia 2
7 2 Nokia 1
8 2 Samsung 5
9 3 Samsung 1
10 3 Samsung 3
Thanks in advance...
L4/5
L4/5
L3/4, L4/5
L4/5
L3/4, L4/5
L3/4, L4/5, L5/S1
L3/4, L4/5
L4/5, L5/S1
L4/5, L5/S1
L5/S1
L3/4, L4/5
L5/S1
L3/4, L4/5
L4/5
L2/3, L3/4
L3/4, L4/5
L2/3, L3/4, L4/5
L4/5, L5/S1
L3/4, L4/5, L5/S1
L5/S1
L3/4, L4/5
I have data that looks like the above (a range of A1:A238)
I want to count for example how many times "L1/2" appears in the range A1:A238. When I use the function =COUNTIF(A1:A238, "L1/2") it gives me the answer two, but I think it is only counting cells that contain only "L1/2" and not perhaps those that contain "L1/2, L2/3, L3/4".
I would like to count every time L1/2 is listed in the range from A1:A238
Thanks in advance!
Rusty:
As you have discovered COUNTIF will not work in this situation. Instead, you'll need to use SUMPRODUCT, ISNUMBER and FIND or SEARCH.
SEARCH is not case sensitive, FIND is case sensitive and both will return a position which ISNUMBER returns as a hit and then SUMPRODUCT returns as the sum of the hits.
The formula will look like this:
=SUMPRODUCT(--(ISNUMBER(SEARCH("L1/2",A1:A238))))
This was VERY helpful. I had to do a little fine tuning, but it gets the job done. Thanks so much.
I have 3 columns. Column 1 is a list of questions. Column 2 is Yes/No/ N/A Dropdown. Column 3 is Score. If column 2 is "Yes", put a 1 in the score column. If column 2 is a "No", put a "0". If column 2 is "N/A", don't populate the cell with anything. What I want to do is have Excel count all cells in column 3 that have a value (1 or 0), then calculate the number of 1's against the full count and place the percentage at the top of the Score Column (Cell F3)
Just an FYI... the first part is already done (Yes/No/N/A translating to a value of 1 or 0). I'm trying to figure out how to calculate the score (by percentage) and place it at the top of the Score column
Dan:
I guess the easiest way to do what you want is to use the functions COUNTIF and COUNTA.
Where the scores are in C51:C57 the formula is:
=COUNTIF(C51:C57,1)
then to get the total number of non-empty cells the formula is: =COUNTA(C51:C57)
So if you enter these formulae in D4 and D5 respectively
the percentage is derived by D4/D5 and format the cell as percentage.
Please help me with this problem. I would like to highlight all rows where the Name and Date column values match AND the Procedures listed on the same date definitely has 921 listed and also has either 992, 993, 994 or a combination of these procedures listed on the same date (Each procedure is listed once in the row, so if they have 921 and 922 on one date that will be two rows). So for each matching name and date there will be at least two rows highlighted. Thank you so much!
Don,
We've received your sample table and replied to you by email. Please check your inbox.
Hi, I am trying to create a formula that would count only dates that are after 12/31/2016 and that do not include NA or the blank cells. Right now I have =COUNTA(AO$7:AO16)-COUNTIFS(AO$7:AO16,"NA", AO7:AO16,">=12/31/2016")
The range in AO7:AO16 is the following:
27-Jun-17
8-Aug-17
na
24-May-16
06-Jun-16
06-Jun-16
The formula is not counting what I need it to count, what am I doing wrong? Are the cells formatted incorrectly?
Thank you!
I have a spreadsheet where patients are admitted at a certain time then discharged a certain time. I need a formula to count the number of patients that are in ER during a 1 hour time.
See table below.
In Column B I have the Admission time and in Column C I have the Discharge time.
If I have 33 pts admitted at different hours throughout the day, I would like a formula to count the number of patients that are in ER at 5:00 then at 6:00 then at 7:00 and so on.
Below is the table I have.
With manual counting I have 2 pts in ER at 5:00, 4 patients in ER at 6:00 and so one. Is there a formula for this.
A B C
Patient Admission Time Discharge Time
1 5:14:00 AM 7:21:00 AM
2 5:29:00 AM 6:33:00 AM
3 6:01:00 AM 9:06:00 AM
4 6:25:00 AM 7:10:00 AM
5 6:42:00 AM 9:45:00 AM
6 6:46:00 AM 8:15:00 AM
7 8:13:00 AM 8:18:00 AM
8 8:32:00 AM 11:15:00 AM
9 8:42:00 AM 11:00:00 AM
10 8:48:00 AM 11:26:00 AM
11 8:54:00 AM 10:42:00 AM
12 9:00:00 AM 11:45:00 AM
13 9:05:00 AM 9:40:00 AM
14 9:52:00 AM 1:40:00 PM
15 10:12:00 AM 1:00:00 PM
16 10:33:00 AM 12:11:00 PM
17 11:02:00 AM 11:21:00 AM
18 11:29:00 AM 3:18:00 PM
19 12:28:00 PM 3:16:00 PM
20 12:50:00 PM 2:00:00 PM
21 1:44:00 PM 3:37:00 PM
22 3:47:00 PM 4:49:00 PM
23 3:58:00 PM 5:10:00 PM
24 5:05:00 PM 5:16:00 PM
25 6:40:00 PM 7:45:00 PM
26 6:55:00 PM 10:11:00 PM
27 7:17:00 PM 9:55:00 PM
28 8:09:00 PM 9:09:00 PM
29 8:15:00 PM 11:00:00 PM
30 9:28:00 PM 10:43:00 PM
31 10:26:00 PM 11:26:00 PM
32 10:49:00 PM 11:00:00 PM
33 11:11:00 PM 11:37:00 PM
Marlene:
If you want to count the number of patients admitted during a particular hour this is how I would do it.
Where the data is in A1:C34 including column labels;
To the right of this data add three columns. The first in D1 is labeled Admin Time, the second in E1 is labeled Sum of Admin Time and the third is in F1 and is labeled Time Wanted. Clearly you can label them as you see fit.
In D2 enter =MROUND(B2,"1:00") this will produce 5:00 using your first sample.
In E2 enter =COUNTIF(D2:D35,$F$2)
In F2 enter the time period you're looking to count.
So, you're telling Excel to look in the range D2 thru D35 for the value in F2. In the formula the F2 address is locked.
The cells holding the time should be formatted in the same type. I used Time 1:30PM for my practice sheet. This allowed me to quickly distinguish between AM and PM times.
I hope this works for you. Let me know if there's something else.
Hi
I have a excel spreadsheet of members of a club I am running, the spreadsheet contains members details each member has a unique reference number ie, 1243199 etc etc, the members may then have joined more than one group within the club.
I need to be able to show how many members are members of either 1, 2, 3, 4, 5, 6, or 7 groups.
Thanks for your help
Simon:
Without having your data in front of me I would say if you select the cells that contain the data you want to analyze and then click Insert/Table you can format the data into a table that you can quickly and easily sort and filter by member or club or whatever information is in the table.
If the columns don't already contain headers for each
piece of information, go ahead and create headers before you create the table. It makes things much easier to work with.
Depending on the number of members in your club, it might also be easy to create a Pivot Table to analyze this.
Base your Pivot Table on a table and the changes in the table will be reflected automatically in the Pivot Table.
There is a good explanation of Pivot Tables here on AbleBits. Just type "Pivot Table" in the search box and you'll see the link to Pivot Table for Beginners article.
HI...
I was tried the formula for sum product
Eg.,
1+2+3+4. this is A Column Values.
I need the Value of Total in B column.
May I know the Formula pls
Abi:
If I understand your question, where the data in Cells A1:A4 are 1,2,3,4 then in Cell B1 the formula is =Sum(A1:A4)
HI...
I was tried the formula for sum product
Eg.,
1+2+3+4. this is A Column Values.
I need the Value of Total is B column.
May I know the Formula pls
Hi,
Is there a way to count sequential cells using COUNT A?
I want to have an auto count simply to see if there is data in the boxes 1,3,5,7,9....
and one for 2,4,6,8,10...
Thank you in advance for any help...
I am try to write a formula that calculates a range of cells if a second range of cells contains a specific word.
Can you help me in writing this formula.
Thanks
If in one cell we will add 14 no e.g. =12+12+15+16+16....than by which formula we can count total entered no ie 14 no??
If in one cell we will add 14 no e.g. =12+12+15+16+16....than by which formula we can count total entered now ie 14 no??
How to be call apple/oranges in one column..!
hi,
please advise how can count multiple department who received quote and status closed
Hi,
I am trying to find the numbers of people from different districts, I have set all the details of people giving cascading options but in the analysis part, I want the numbers of districts chosen by the data collector, is there any particular formula? so that I can get these numbers of people were from this district and these from another district.
Thanks
Suresh,
how to keep attendance daily wise even multiple visit count 1 per day in excel please help me...
Name Date Visit
Ram 2017/12/1 1
Shyam 2017/12/1 1
Hari 2017/12/1 1
Ram 2017/12/1 1
Ram 2017/12/2 1
shyam 2017/12/2 1
Ram 2017/12/31 1
shyam 2017/12/31 1
answer should be
name month attendance
ram December 3
Shyam December 3
Hari December 1
how to do????
Hi,
How to count text mentioned as ''yes'' in different cells Example : count of Yes in cell No. A2 +A20+B15+c30+D25
Please help
Hello,
Please try the following formula:
=COUNTIF(A2,"=yes")+COUNTIF(A20,"=yes")+COUNTIF(B15,"=yes")+COUNTIF(C30,"=yes")+COUNTIF(D25,"=yes")
Hope it will help you.
Hello Hi
Please sent my formoula
for exmplale
500-2=498 after one entry amount 498 next new figer enter in cell for
exmpale 3 = 498-3= 495 amount
plese sent me simple formoula
Im trying to create a formula that counts yes and no without counting them more than once. The yes can apply to the same person but only need to count it once.
Dear,
How to use countifs function in I have sevaral answers in one column that will be counted?
More precisely, if I want to count only "Yes" answers from one column and "Yes, always" and "Yes, sometimes" answers from another.
Thank you in advance...
Hello,
2 questions:
1. How to count cells in a row with certain color/s (I use only 2 colors) and return the result in another tab (summary).
2. How to make sure that additional rows in a sheet are counted? I can't highlight the whole column (as range) as I include notes at the bottom of the working area. At the moment, I specify a range but when my list goes beyond the range, and I forget to update the formula, I miss the additional rows and my count is wrong.
Appreciate the help. Thank you.
Hello, Sarah,
1 Here's a short tutorial about counting cells depending on their colours :)
2 Perhaps, you could extend the range in the formula, but I'm not entirely sure whether you need all the cells from additional range to count or only some of them. And if only some of them, how you decide which ones?
Hi, I'm looking for a formula where it only COUNTS a certain criteria in Sheet 2 if it matches my formula in Sheet 1 B2 which is: =COUNTIF(Sheet 2!$A$1:$A$120,"Dog")
So imagine Sheet 1 B2 having the results already, I now wanted to count either A, B, or C in Sheet 2 if it EXACTLY matches the name I'm looking for in Sheet 1 A1.
e.g: Sheet 1 A1=Dog B1=(count of Dog in Sheet 2) C1=A D1=B E1=C
Sheet 2 A1,A4,A7,A10,A11=Dog
Sheet 2 A2,A5,A8,A12=Cat
Sheet 2 A3,A6,A9,A13=Sheep
Sheet 2 B1=A C1=A D1=A
Sheet 2 B4=B C4=A D4=A
Sheet 2 B7=B C4=B D4=A
Sheet 2 B10=A C10=A D10=A
Sheet 2 B11=A C11=B D11=A
So with this sample given, Sheet 1 B1 counted all "Dog" in Sheet 2. Sheet 1 B1,C1, and D1 is looking for A, B, and C, if all rows in in Sheet 2 says:
[Dog][A][A][A]
[Dog][B][A][A]
[Dog][B][B][A]
[Dog][A][A][A]
[Dog][A][B][A]
Thank you!!!
Can someone help me?
I need to Formal for Countif Formal
Ex : Our Employees Duty Roster so Shift Based Job thats reason i Count Different Cell Select then Count How many Employees there Time was There LIke Please Help me
Thank you so much for your reply, really appreciate it. I tried the formula but it doesnt give me what am looking for.
This is the summary of the formula i need.
discount of $37.9/MT for every pound below 49lbs to 47lbs. And discount of $75.8/MT below 47lbs to 46lbs and rejection for outturn below 46lbs.
Discount of $5/MT for every nut above 210 to 215, and above 215 to be rejected.
Discount of $20/MT for every 1% above 10% to 12% above 12% to be rejected. please i need this formula assistance. Thank you.
Please i need a formula for a discount of $37.9 given when a purchase below 49lbs to 47lbs and also a discount of $75.8 is given when a purchase below 47lbs to 46lbs is been made. please i need a formula fo this please
Hello.
Thank you for contacting us.
Please try the following formula to solve your task:
=IF(AND(A1 >= 47,A1 <= 49), "$ 37.9", IF(AND(A1 >= 46, A1<47), "$ 75.8", 0))
hello !
I need more excel work in today, vlookup and count
thanks in advance
many number count
Can someone help me?
I have a column that has different cities in each cell some have just one city and some have Multiple destinations: city1, city2, city3. These cities overlap. Is there a function I can use to count all of "city 1"? I have tried =COUNTIF(F4:F29,"City 1 name") but it does not count the ones with multiple cities.
Thanks,
Could someone please suggest a formula for the following requirement:
For Example: I have in Column range from B5 to B20 and B5, B6, B7, are with numerical data, and B8, B9 are blank, B10,B11 again with data.
I want to have a formula which counts all the cells from B5 to the last active column which is B11 and the answer should be 7 for this – which is by counting all the way from B5 to B11. Many thanks.
Dear all,
I just got the formula which I was looking for, from another site:
The following formula gives count of all the blank and non-blank, till the last cell with any data:
=MAX(IFERROR(MATCH("ž", A:A),0),IFERROR(MATCH(1E+100, A:A),0))
The following formula give the count of all the blank and non-blank cells till the last cell with the figures
=MATCH(1E+100,A:A)
Hope this would be helpful to someone with the same requirement as mine. God Bless. Thomas
I want to count values those are in following ranges
0 -19 ,20-34, 35-39,40-49,50-64,65-74,>75
Svetlana - thank you for your blog. Your help on an array three years ago helped me greatly on my project. I have a new COUNTIF question. I have a file with 9895 rows of mailing information. The zip codes are either 5-digits or zip+4. I'm trying to count the number of zip codes in a few categories: 1) Number of 5-digit zip codes (I wrote that formula fine); 2) Number of zip codes that BEGIN with THREE specific numbers and DO NOT include any of the 5-digit zip codes I already counted. I can't seem to write a formula or array that gets me to the correct answer. I don't want to count any zip codes that INCLUDE the three specific numbers if they are NOT the first three numbers. I've used LEFT for a few formulas, but now I can't seem to eliminate those zip codes where the specific string is INSIDE the zip code, not the first three characters in the cell. Should the column of zip codes be a number or text? Here's my array attempt at counting: =SUM((LEN(LABELS!$B$2:$B$10000)-LEN(SUBSTITUTE(LABELS!$B$2:$B$9896,"958","")))/LEN("958"))-G2-G3-G4-G7-G8-G9-G10-G11-G12 that I thought searches for zip codes that begin with "958" and subtracts the 5-digit searches I already completed, but it's returning a count with the "958" included in the zip. Any thoughts?
Hello
I havew in my column A the fellowing values
B-COM-TES-001
V-MAN-ITF-005
S-COM-TES-020
V-COM-TES-008
if I want to count the combine (COM-TES)In all the column
How can I do
Merci
I have date in cell like below:
TC1D 2
TC1-D 5
I want to count the date cell for both at a time and sum of the same.
In that way like my count will come 2 and sum 7.
Please help.
=COUNTIF(J4:J7,"*com*")
j4:j7 is data coloumn.
Hello,
Do you also have a tutorial on COUNT and COUNTA in Google Sheets?
Yours,
Robert
Hi there,
I want to count the number of blank cells and non-blank cells in column B,
if column A is X and column C is X, Y and Z.
Thank you
I have data with family includes Self, spouse, children, parents. i want separate count of how many single child family and how may 2 children family are there in data.
kindly help
Hi,
I would love to have some help on this:
I have B5:B66 range and I want count ONLY if a certain value occurs 3 times or more (values 8,0
Example: 8599962555
I need to count: 999 = 1
and: 555 = 1
Then : 1+1 = 2
How can I do this???
thanks in advance!
Extremely Helpful website.
Hi, i have a certain name (wilium) in column A and against this name i have 2 option, 1 Done and 2 Inprogress. i want that i count done again wilium and count inprogress against wilium separately. which formula will work for it??
how can I make formulas appear in all cells instead of the values
ctrl+`
I have query divide this no as an following way in different column if 4,50,00, 000 given then we will get
2,00,00,000
1,00,00,000
1,00,00,000
50,00,000
Ok guys/girls i'm pulling my hair out with this formula, i'm fairly confident the problem is in the two cells with times in. cells h50:h5000 have two times combined in a cell with - as a division, all i need is the second part but using 'right' function doesn't appear to work as part of a larger countifs. these cells are imported and can be 1000's of rows long so creating another column to separate is awkward.
f50:f5000=13:59 h50:h5000=12:33-17:00
=COUNTIFS(C$50:C$5000,A6, X$50:X$5000, ">0", Y$50:Y$5000, ">0",F$50:F$5000, ">" & RIGHT(H$50:H$5000,5))
The formula by itself (ctrl, shift, enter) appears to work fine but I require the other criteria.
=COUNTIF(F$50:F$5000, ">" & RIGHT(H$50:H$5000,5))
Any help would be greatly appreciated.
Andy