Andy wonders why the COUNT function doesn't count text entries. Episode 955 will show you how to overcome this with COUNTA.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically we start out with massive amounts of data.
So how we're gonna analyze as well let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question. Andy from Cincinnati says he has a data set like this, and he needs to count number of occurrences in each row =COUNT, and since so I'm sure you've used this function before.
And it counts how many items we have.
But here's Andy's real problem.
His data is not numeric like this data is.
I'm going to Ctrl drag, will make a copy of that.
Andy's data doesn't have 1 it actually has some text.
So we use "Edit" "Replace" and change your occurrence of a 1 to something just a word, Yes, let's say.
"Replace All" Ok, and you see the COUNT function completely fails.
Now COUNT cannot deal with text and so Andy says, Well you know, I need to count these cells.
How can I count them when they are text entries?
Well, It's not entirely obvious.
COUNT is designed to count only numeric items, there is a second function called COUNTA COUNTA will count both numeric and text.
It's basically counting all of the non blank cells.
So if you need to get a total count you can use that.
I'll just add it 1 here and you'll see that the 4 change to a 5, so it does count both numeric items and non numeric.
So just a quick recap if we want to count the numbers then we use =COUNT.
If you want to count everything then use COUNTA.
If you want to count only the text, then I guess we're actually going to use two things.
We're using COUNTA minus the COUNT and you're going to get the entries that are text or "True" "False" and so on.
So there you have it.
A simple little question if you know the answer.
If you don't know the answer the COUNT function is very very frustrating not counting text items then use COUNTA to handle that.
Want to thank Andy for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Basically we start out with massive amounts of data.
So how we're gonna analyze as well let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question. Andy from Cincinnati says he has a data set like this, and he needs to count number of occurrences in each row =COUNT, and since so I'm sure you've used this function before.
And it counts how many items we have.
But here's Andy's real problem.
His data is not numeric like this data is.
I'm going to Ctrl drag, will make a copy of that.
Andy's data doesn't have 1 it actually has some text.
So we use "Edit" "Replace" and change your occurrence of a 1 to something just a word, Yes, let's say.
"Replace All" Ok, and you see the COUNT function completely fails.
Now COUNT cannot deal with text and so Andy says, Well you know, I need to count these cells.
How can I count them when they are text entries?
Well, It's not entirely obvious.
COUNT is designed to count only numeric items, there is a second function called COUNTA COUNTA will count both numeric and text.
It's basically counting all of the non blank cells.
So if you need to get a total count you can use that.
I'll just add it 1 here and you'll see that the 4 change to a 5, so it does count both numeric items and non numeric.
So just a quick recap if we want to count the numbers then we use =COUNT.
If you want to count everything then use COUNTA.
If you want to count only the text, then I guess we're actually going to use two things.
We're using COUNTA minus the COUNT and you're going to get the entries that are text or "True" "False" and so on.
So there you have it.
A simple little question if you know the answer.
If you don't know the answer the COUNT function is very very frustrating not counting text items then use COUNTA to handle that.
Want to thank Andy for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
