Giving "X" a numerical value.

[floyd.anderson]

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.

 

[Jarek Kujawa]

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

 

[Lars-Åke Aspelin]

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

 

[Mike H]

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

 

[Gary''s Student]

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

 

[Glenn]

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.

 

[Gord Dibben]

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

Is that what you need?


Gord Dibben MS Excel MVP

 

[Jarek Kujawa]

this one is excellent
will remember it
;-)

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.