Giving "X" a numerical value.
 

I am trying to build an active personnel spreadsheet. With the individuals
names listed in column "A", I need to give "X" a numerical value so that when
I add the X to account for this individual it will give the total active at
the bottom. I know the formula for the simple addition and understand how to
do that, however I can not figure out how to give a text a numerical value.

Giving "X" a numerical value.
 

if I understand you correctly

=SUM(IF(B1:B100="X",1,))

should help and give you the toal of active personnel (if X's are in
col B)

CTRL+SHIFT+ENTER (instead of using ENTER only) this formula as this is
an array formula


On 11 Gru, 21:40, floyd.anderson
wrote:
I am trying to build an active personnel spreadsheet. With the individuals
names listed in column "A", I need to give "X" a numerical value so that when
I add the X to account for this individual it will give the total active at
the bottom. I know the formula for the simple addition and understand how to
do that, however I can not figure out how to give a text a numerical value.

Giving "X" a numerical value.
 

On Thu, 11 Dec 2008 12:40:01 -0800, floyd.anderson
wrote:

I am trying to build an active personnel spreadsheet. With the individuals
names listed in column "A", I need to give "X" a numerical value so that when
I add the X to account for this individual it will give the total active at
the bottom. I know the formula for the simple addition and understand how to
do that, however I can not figure out how to give a text a numerical value.


What is "X"? Is it the column "X" or is it some name that is in
column "A"?
What do you mean by "when I add the X to account for this
individual..."? How do you "add"? Where do you "add"?

Please give an example of data, before and after the "adding", and
what result you expect and where you expect the result.

/ Lars-Åke

Giving "X" a numerical value.
 

Hi,

You've lost me perhaps you could post a small sample of data with the result
you expect with an X in the sum range.

Mike

"floyd.anderson" wrote:

I am trying to build an active personnel spreadsheet. With the individuals
names listed in column "A", I need to give "X" a numerical value so that when
I add the X to account for this individual it will give the total active at
the bottom. I know the formula for the simple addition and understand how to
do that, however I can not figure out how to give a text a numerical value.

Giving "X" a numerical value.
 

Say column A has names and there are some X's in column B. We want the sum
of X's assuming X has the value 17:

=SUMPRODUCT(--(B1:B100="X"))*17

--
Gary''s Student - gsnu200820

Giving "X" a numerical value.
 

floyd.anderson wrote:
I am trying to build an active personnel spreadsheet. With the individuals
names listed in column "A", I need to give "X" a numerical value so that when
I add the X to account for this individual it will give the total active at
the bottom. I know the formula for the simple addition and understand how to
do that, however I can not figure out how to give a text a numerical value.


To come at this another way, you can make the number 1 display as X. Use this
as a custom number format:

[=1]"X";0

Then you can just SUM() the range to get a total.

Giving "X" a numerical value.
 

=COUNTIF(A1:A100,"X") will count all the "X"'s

Is that what you need?


Gord Dibben MS Excel MVP

On Thu, 11 Dec 2008 12:40:01 -0800, floyd.anderson
wrote:

I am trying to build an active personnel spreadsheet. With the individuals
names listed in column "A", I need to give "X" a numerical value so that when
I add the X to account for this individual it will give the total active at
the bottom. I know the formula for the simple addition and understand how to
do that, however I can not figure out how to give a text a numerical value.

Giving "X" a numerical value.
 

this one is excellent
will remember it
;-)

On 11 Gru, 22:20, Glenn wrote:
floyd.anderson wrote:
I am trying to build an active personnel spreadsheet. With the individuals
names listed in column "A", I need to give "X" a numerical value so that when
I add the X to account for this individual it will give the total active at
the bottom. I know the formula for the simple addition and understand how to
do that, however I can not figure out how to give a text a numerical value.

To come at this another way, you can make the number 1 display as X. *Use this
as a custom number format:

[=1]"X";0

Then you can just SUM() the range to get a total.