make a text string equal a number

[Guest]

Hello All,

I am currently creating a spreadsheet for an Activity Report for our
company. I am trying to create a formula to calculate the total number of
participants involved in particular programs. I would like the formula to
cauclate as follows: If the cell referenced contains text (i.e. a person's
name), then that cell's numeric value would be equal to 1. Then I would be
able to make a sum based on the number of people who participate in that
particular program. If the cell was blank, then the numerica value of that
cell would be zero, and would not affect the total number of participants.
Is there any existing formula that would calculate this, or are there any
alternative solutions to doing so? Any assistance in this matter would be
greatly appreciated.

 

[JulieD]

Hi

see your other post under the username of creativsparx in this forum

Cheers
JulieD

 

[Govind]

Refer to the other post from Creativesparx

Regards

Govind.

 

[Bernie Deitrick]

Carlos,

Take a look at the COUNTA function, used like

=COUNTA(A:A)

to count the number of text-filled cells in column A.

If you also have numbers in column A, and only want the count of text
values, use

=COUNTA(A:A)-COUNT(A:A)

For either of these, you may need to subtract 1 for each header value.

HTH,
Bernie
MS Excel MVP