Initially, Excel spreadsheets were designed to work with numbers. But these days we often use them to store and manipulate text too. Want to know how many cells with text there are in your worksheet? Microsoft Excel has several functions for this. Continue reading
Comments on: How to count cells with text in Excel: any, specific, filtered cells
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 100
Hi,
Good day!
Please help me to correct this formula for monthly timesheet summary:
If(‘May2021’!C:C,”=Tony”, countif(‘May2021’!D:Z,”=Present”)
Thank you so much
Hello!
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.
This should solve your task.
HI,
Good day!
can you help me if this is possible to do in excel, I want to count the number of cells containing the specific value that is within a specific row or column that is in between a specific value in a cell.
i.e. COUNT THE CELLS IN A ROW BETWEEN "JAN"
A B C D E F G H I
1 TOTAL COUNT
2 JAN FEB MAR OCT SEPT MAR JAN AUG 5
3 FEB JAN DEC FEB JAN APR MAR OCT 2
4
I.E. COUNT THE NUMBER OF CELLS HAVING "P" BETWEEN "O".
A B C D E F G H I
1 TOTAL COUNT
2 O P P P P P O P 5
3 P O P P O P P P 2
4
I have a spreadsheet that has a column for each month (12 columns) and a row for each day (31 rows), I have put in each column and row a letter to track type of days off, I want to total the types of days off on a separate spreadsheet. Is there a formula that will do this for me?
Hello!
Here is the article that may be helpful to you: Excel formula to count cells with certain text.
Hi,
I was wondering if there is a way to count the total number of entries for one certain type of information, I have a spreadsheet with over 500 entries but I need to know the total entries for one particular key word
hope this makes sense
please advise
thank you
Hello!
Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.
Hallo,
I have a table with 10 columns and 240 rows. Every cell contains a product in text format. Is there any formula to count the total number of every product in the whole table without putting any criteria for searching any product. Because the products are more than 100.
Thanks
Hello!
Give an example of the source data and the expected result. Do you want to get a list of all products along with the quantity of each product? For this I recommend using a pivot table. If this is not what you wanted, please explain the problem in more detail.
It's not visible. Now I think it's better.
Database
A B D F G
B C A I H
C A B L A
E F G C A
K I H E F
L A M A I
…. …. …. …. ….
…. …. …. …. ….
…. …. …. …. ….
…. …. …. …. ….
…. …. …. …. ….
…. …. …. …. ….
…. …. …. …. ….
…. …. …. …. ….
…. …. …. …. ….
Final table
A number
B number
C number
D number
E number
F number
… number
… number
… number
Hello!
Use this formula to extract unique values from a range.
Assuming your values are in the range A2: C9, enter the following formula in cell E2:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"") * (COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
In the above formula, A2:C9 indicates the range of cells from which you want to extract unique values. E1 is the first cell in the column where you want to place the result. $2:$9 means strings containing cells you want to use. $A:$C points to the columns containing the cells you want to use. Please change them to your own.
Press Shift + Ctrl + Enter and then drag the fill handle to extract unique values until blank cells appear.
To count the number of each unique value in a range use the formula
=SUM(--(TEXT($A$2:$C$9,"General")=TEXT(E2,"General")))
I hope I answered your question. If something is still unclear, please feel free to ask.
Thanks for your help!
I entered the first formula exactly as you explained.
It gives me an error in the part "E1,$A$2"
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9"") * (COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
Rgds
Ivan
$C$9
Actually above formula works but u have to cut space between in =INDIRECT(TEXT(MIN(IF(($A$2:$C$9 ""). Beyond that you have to put this function into E2 cell (not E1) and then stretch to bottom cells until unique values will stop appearing. It was achieved by trials and errors, so I cant explain why it so, but it works.
doesn't appear correctly in the post here
Hi,
What separators do you use in your formulas? I guess it's a semicolon. Consider your regional settings.
The symbols higher and lower do not appear in the post. I don't know why. It suggests me a formula only with the symbol lower.
Hi,
The part of the formula $C$9 "" is not accepted. It suggests me a formula with $C$9 < "" only. Even with this element the cell E2 remains blank.
Ivan
Hi,
I copied your new formula in the cell E2. When pressing Shift + Ctrl + Enter it appears a message that says me that $C$9 is incorrect and suggests me a formula with $C$9 < only. Even with this element the cell E2 remains blank.
Rgds
Ivan
Hi,
I changed the commas in the formula to a semicolon.
Now the formula shows me an error in the part $C$9""
Rgds
Ivan
Hi!
Copy the formula carefully. Check all signs. I repeat it one more time
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9 < > "")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
$C$9"" is incorrect. Need to $C$9 < > ""
Remember to press Shift + Ctrl + Enter. This is an array formula.
Hi,
yes, the separator is a semicolon. Should I change it?
May I send you my table via e-mail in order that you put the formulas. Which e-mail?
Thanks
Ivan
Hi,
Change the commas in the formula to a semicolon.
Hello,
By using a pivot table I can count column by column. I want to count the numbers from all columns automatically without specifying a criteria for each product. Here is the example:
Database Final table
A B D F G A number
B C A I H B number
C A B L A C number
E F G C A D number
K I H E F E number
L A M A I F number
…. …. …. …. …. … number
…. …. …. …. …. … number
…. …. …. …. …. … number
…. …. …. …. …. … number
…. …. …. …. …. … number
…. …. …. …. …. … number
…. …. …. …. …. … number
…. …. …. …. …. … number
…. …. …. …. …. … number
I may have missed this in your above info, but how do I count a row of 'text' that contains both dates and text? I am trying to get a subtotal of tasks undertaken by a list of clients.
Hello!
If I understand your task correctly, You cannot perform mathematical operations on text. Therefore, you must first select the dates or numbers that you need from the text. The information you provided is insufficient for a more accurate answer.
Is there any why to use SUBTOTAL with COUNTIF?
I would like to count cells with text from a filtered list of text.
I know how to do this with adding numbers but not counting text.
Thank you,
Ray
A lot of useful information,
Thank you,
Ray
Hello
Need some help Please
let say i have 4 sheet name (A,B,C & D)
it is actually days shift report , mean same format but different fault log of equipments.
let say there are 4 equipments ( Eq-1,Eq-2,Eq-3 & Eq-4) in one colum and second colum has its different fault types like( brake fault, hoist fault, run fault etc..)
same fault log could be in all sheets (A,B,C & D).
now on one sheet say summary i want to count Eq-1 brake fault what how many time it occurs total in all 4 sheets.
i hope i have explained details about my requirement.
Please help me.
Thanks,
asif
Hello,
From the example of =COUNTIF(A2:A7, "bananas")
What formula would I use to have that same row count BANANAS and APPLES at the same time.
thank you in advance.
Hi Ana,
If my understanding is correct the task is to count cells in A2:A7 that contain either "bananas" or "apples".
The easiest solution is to add up 2 COUNTIF functions:
=COUNTIF(A2:A7, "bananas") + COUNTIF(A2:A7, "apples")
Or you can use a SUM COUNTIF formula with an array constant:
=SUM(COUNTIF(A2:A7, {"apples","bananas"}))
For full details, please see COUNTIF and COUNTIFS with multiple OR conditions.
Hey there
I have a sheet that in column B has the location (ex, Cali/ Texas) and the column D - I tally all the ones from Cali aka California and Column E - I tally all the ones from Texas
Ex:
Col B Col D Col E
Cali 1
Texas 1
- My question is, is there a way to have column D auto generate a "1" when column B states Cali and vise versa for column E auto generate a "1" when column B states Texas.
Hi, I have a sheet which I'm using to calculate how many sales are from which or 10 companies. Column B has customers names, column aa has the main company name, column aj is closed (not sold) and column ao has the dates something was sold. I have a table on a separate tab with this formula: =COUNTIF(Main!AA:AA,A2) main is the tab name, AA the column and A2 is the cell with the company name I'd like to count. My question is, what formula what I use if I wanted to count these names only if sold (based on any text in column ao), closed (based on any text in column aj) and still outstanding based on no text in ao or aj. Hopefully that makes sense! Thanks
ps. I should point out that I'm counting the number of cells, not the value within those cells as they all contain text or dates. But if it is also possible to count the value of columns AL, AM, and AN based on the company name in column AA, then that would be amazing.
I have a column with various names and another column with various statuses attached to those names. I want to distinguish specific statuses attached to these names.
E. G.
Column A and B will look like this:
Mr A. Status complete
Mr A. Status outstanding
Mr A status paid
MR B status outstanding
MR B status complete
I need to which of these rows per name contain a combination of statuses complete and paid.
In example above Mr A must be shown to have a status in 2 of the rows and Mr B will not show up anything.
How do I go about this?
HOW TO USE A FUNCTION THAT WILL CALCULATE THE TOTAL NUMBEROF PRODUCT THAT ARE IN TEXT
Hello!
Here is the article that may be helpful to you: How to count words in Excel
I hope it’ll be helpful.
I have attendance sheet to prepare so I want to count number of p in the same row from 1st September to 30th September. how do I do it?
Hello!
The information presented by you is not enough to give you advice. I recommend doing a conditional count using the COUNTIF function.
Hi Team
I have been battling for ages to get this formula right,
=COUNTIF('Day 1:Day 31'!J8:J25,"CORGI")
I have a 12 mth workbook with and column J8:j25 that has names in text ie Corgi,Sandra,Stuart,House etc.
i can use the countif to find the individual totals on a single work sheet but that takes up a lot of time and space , i am looking the formula to use on a summary sheet and this is the best thing i have come up with but does not work, for some reason the J8:j25 just relates to the summary sheet i iam working on.
could you please shed some light on where i am going wrong?
Many thanks
Robert
Hello!
Unfortunately the 3D link cannot be used in the COUNTIFS function. Here is a list of the 3D supported functions.
Use the COUNTIF function for each worksheet separately.
Hello, I'm trying to get a row to tally if a range has either P,S, or 2 within the range. Is there a way to accomplish this?
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
Im looking for a formula to give me only the number of managers I have working projects. Each manager has multiple projects they work on. Manager names are in column A. Projects in B. I only want to know the total managers I have, not the projects I have. For instance, John's name is listed 8 times, Mike's name is listed 6 and Jill's name is listed 7 times. The answer I want is 3.
Hello!
I recommend that you study our guide to calculate how many unique values are in the list.
Is there a formula that will count all cells containing text within a specific range except cells that contain certain a certain word or character (in my case, I need a count that includes everything in the spreadsheet except blank cells and cells that contain "X"). Thank you!
hello sir,
i have a 03 criterias (higher,lower and standard)in a column which ranges from (8-34200 rows)i also have date column now my problem is whenever i makes a filter in date for example if i want to check how many higher ,lower cases are occured on particular date
Hello!
If I understand your problem correctly, you need to use the COUNTIFS function to count. Read more here.
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
hi. Need help.
I have a row of numbers and text "IN" (in different cells).
What formula do I use to count the total if any of the cells in the row have numbers and IN.
Thank you in advance
Hello!
If I understand your task correctly, please try the SUM formula. It ignores text and only sums numbers. You can read more in this article.
Good Day Sir,
My question is this. I have a list of all "Fruits" in column A. Those "Fruits" count for many row entries each. I would like to count how many rows each "Fruit" totals to. Lets say I have "Apple" 50 times, "Banana" 20 Times and each "Fruit" has a different number of appearances and there are a LOT of different "Fruit". My goal is to create a list of how many times the "Fruit" shows on my spreadsheet in a certain Column and output a count by Type of "Fruit". So I get a list of How many times "Apple" shows or "Banana" Shows.
Hello J. Merry!
You can use the COUNTIF function, for example = COUNTIF (D2: D10, "Apple"). You can read more about this on our blog in more detail. However, I recommended using a pivot table. Read more about pivot tables here.
Sir
I have row of duty roaster which contains DO RD, and I wanted to count number of DO till just it change to RD then count the number RD till just change to OD then again counting the remaining ODs for the rest of the month.
I can sen you my time sheet for clearer info.
Hello Talal!
Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.