Tom’s Tutorials For Excel: Counting All Data Types in a Range
Any data that you put in a cell will always fall into one of four types:
• Text
• Numeric
• Boolean (TRUE or FALSE)
• Error
In the picture, you see a representation of all these data types in column A. The table in the colored cells of range C2:E6 contain formulas that count each of these four data types, along with a formula that counts the full collection of all cell data type entries.
The formula in yellow cell E2 that counts text only is
=COUNTIF(A3:A28,"*?")
The formula in brown cell E3 that counts numbers only is
=COUNT(A3:A28)
The formula in blue cell E4 that counts boolean TRUE/FALSE only is
=SUM(COUNTIF(A3:A28,{TRUE,FALSE}))
The array formula in pink cell E5 that counts errors only is
=SUM(IF(ISERROR(A3:A28),1))
Recall that an array formula is applied to the cell with Ctrl+Shift+Enter, not just with Enter.
The formula in gray cell E6 that counts all data type entries is
=COUNTA(A3:A28)
Hi Tom,
I am almost done with my spreadsheet and I am hung up on the last formula I will need, I think. I have been trying to get it to work for the past two days in different variations. I need to count the number of par’s=3, birdies=2, and so on for individual players. What is this missing?
{=COUNTIF(4:12,HLOOKUP(“Bott”,1:3,3,0))}
Can you say what columns have what sample data in them and what your expected result would be from a small sized example? I cannot tell based on your formula. Kind of looks like your data is in rows and not columns but if you explain further I can better assist.
In cells F:K I have 8 names in drop down boxes in each cell. So any name can be selected in each cell since not everyone plays every round. Each hole is listed vertically. If my name is selected in cell F My scores will go under my name vertically 4:12 for front 9 and 16:24 for the back nine.
The score cards will be duplicated over and over again going across the spread sheet every time we play. I want to count how many par’s and birdies a person gets per hole which will add them all up on a Summary page.
So far all i can get it to do is count my name each time my name is selected.
What possible scores are in those cells, meaning what exact entries are made in those cells, and how are parts and birdies identified by those entries. It’s my understanding that different holes on a golf course have varying difficulty and hence varying stroke counts that would indicate what a par is and what a birdie is and what an eagle is for that hole
Oh Sorry,
The value range is:
Ace = 1
Birdie = 2
Par = 3
Bogie = 4
Double Bogie = 5
Triple Bogie = 6
Dirt Dawg = 6D *This is a term we came up with if you go over 6. Highest score possible is 6.
The course is a par three golf course so holes 1-18 are all 3 for par.
Let’s say cells F3:K3 contain the players’ names. As a simple example, if there are no numbers in cells F13:K15, then it is a simple matter of counting each value range per player.
In some cell such as F26 can be the formula
=F3 & ” Ace: ” &COUNTIF(F4:F24,”=1″)
Repeat the syntax for cells F27:F32 for each of the value ranges.
Then copy F26:F32 across as needed.
Hi Tom,
The formula works good if the data is going vertical down F3. I am not sure how to work with the code for it to recognize if that cell says Bott or Will. The cell could potentially be 1 of 6 different names. There is also a break in the data range F4 to F12 and F16 To F24. F13 and F14 could potential have a number in them that I would not want to count.
Is there a way to send you a screen shot example so you could see the setup i have?
My email address is on the Contact page.
Hi Tom,
I sent you an email the other day with some screen shots. I am still trying to figure it out myself and have tried a few different variations of Count Ifs since sending, they did not work. Do you think a pivot table may be my answer?
I sent you a reply email a few days ago.
What does your email show up as? I have not had anything come through. It could be in my SPAM.
Phonetically:
tom
at
atlaspm
dot
com
I do not have anything in my email (cbott85@gmail.com). I looked in every mail box possible and have nothing with your name on it other than my original email.
Resent as a forward on August 26 at 4:07 PM after sending it first on August 19, 2018 at 4:48 PM.