Ask Experts Questions for FREE Help !
Ask
    K4IE's Avatar
    K4IE Posts: 2, Reputation: 1
    New Member
    		  
    #1

    Dec 3, 2008, 03:38 PM
    Using Count to get the number of cells in a column that contain text
    I'm trying to just count the number of cells in a column that have text in them. I used a formula given in one of the previous questions and got a return of #NUM.
    The fomula I tried was =Sumproduct((Callsign <>"")/Count if (Callsign, Callsign & ""))
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #2

    Dec 3, 2008, 03:42 PM

    Try this, in a separate column put the formula:

    =IF(IsText(cell),1,0)

    then just sum that column.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #3

    Dec 3, 2008, 05:10 PM

    This is the formula you want:
    Code:
    =COUNTIF(CallSign,"*")
    mdosh01's Avatar
    mdosh01 Posts: 64, Reputation: 8
    Junior Member
    		  
    #4

    Dec 4, 2008, 06:21 AM
    Just to throw in another option, if by "text" you mean cells that are not empty, you can use:

    =counta(Callsign)

    Assuming "Callsign" is the range in question.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #5

    Dec 5, 2008, 01:49 PM
    Comments on this post
    K4IE disagrees: I may have done something wrong, but it returned a 0. I used another suggestion =countif(callsign, "*" ) and that one worked

    First, may I call your attention to the guidelines for using the comments feature found here:

    https://www.askmehelpdesk.com/feedba...ure-24951.html

    As you note, you may have done something wrong, so why did you give me a negative comment?

    I had actually tested this before answering. I've attached a copy of a spreadsheet that shows that it works. In column A, I placed a text a blank and a number. In column B I placed the same formula I gave you in the 3 rows. Only the column containing a text value returned a 1.
    Attached Files
  1. File Type: xls K4IE.xls (6.0 KB, 394 views)
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel 15 digit issue, tried converting to text, text to column feature negates fix [ 6 Answers ]

I have the following numbers that exceed 15 characters that needs to be split into its own columns. Down the road, there would be thousands of such rows of data with the first couple set of unique numbers. 890432453253208820,5004500558,05CC,1,0,0,0,0,0,0, 0000,5.0000,2007-01-11...

Returning text from several cells into one cell [ 5 Answers ]

I would like to return the following information Cell A1 Cell B1 Cell C1 One Two Three In one cell. The answer could be a combination of the possible combinations. Obviously the formula may have some condiational statements (IF) as well as a macro that concactenates. One: Two:...

Excel - Count how many cells have a certain color [ 1 Answers ]

Is there a formula to count how many cells within a row have a certain color. For Instance if I have A1 to M1 filled in with text and 8 are colored yellow then I want my result in N1 to say 8. Any way to do this?

How to count cells with colored font. [ 2 Answers ]

I'm have a spread sheet in Excel that has some cells fonts in red to indicate poor numbers. I want to know how I can count how many cells on the spread sheet have red font. I tried to do a COUNTIF command but I'm not sure if this is will count the colored font data.

How to count the cells in excel with different fill pattern styles [ 2 Answers ]

Hi I want to count the cells in excel with different fill pattern styles. How is it possible. Can anyone post the function for this purpose like counting coloured cells. I tried a lot , but I got failed. Here I got a function to count cells filled solid, but it is not working... is it...

View more questions Search