Tom’s Tutorials For Excel: Counting All Data Types in a Range

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)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
14 comments on “Tom’s Tutorials For Excel: Counting All Data Types in a Range
  1. Chris says:

    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))}

    • Tom Urtis says:

      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.

  2. Chris says:

    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.

    • Tom Urtis says:

      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

  3. Chris says:

    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.

    • Tom Urtis says:

      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.

  4. Chris says:

    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?

  5. Chris says:

    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?

  6. Chris says:

    What does your email show up as? I have not had anything come through. It could be in my SPAM.

  7. Chris says:

    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.

Leave a Reply to Tom Urtis Cancel reply

Your email address will not be published. Required fields are marked *

*