assign a value to text
 

I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column for
that training event rather then 9.75 for example. But we also want to be able
to add up all training for that individual for the year and see what we paid
them.

The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

Something like

=SUMPRODUCT(--(A2:A200="Bill"),--(C2:C200="x"))*dollar_amount

--
HTH

Bob Phillips

"Galiant" wrote in message
...
I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column
for
that training event rather then 9.75 for example. But we also want to be
able
to add up all training for that individual for the year and see what we
paid
them.

The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a
hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

Hey bob,

THanks... looks good, but as I said not very savy with excel. It looks like
the first part says all entries in colum A=Bill -what is the purpose of this?
then the the next part looks like what I was looking for it takes all
entires in colum C and says the value= x (i think that is what htat means)
and then the last part mulitplies it by the dollar value?

Is this put where I want my total to be?
and what is the "--" for?
2n question what is the purpose of using (A2:A200="Bill")

THanks!

"Bob Phillips" wrote:

Something like

=SUMPRODUCT(--(A2:A200="Bill"),--(C2:C200="x"))*dollar_amount

--
HTH

Bob Phillips

"Galiant" wrote in message
...
I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column
for
that training event rather then 9.75 for example. But we also want to be
able
to add up all training for that individual for the year and see what we
paid
them.

The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a
hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

I just gave the solutions a try and it is giving me some cirucular reference
issue?
not sure about that

"Bob Phillips" wrote:

Something like

=SUMPRODUCT(--(A2:A200="Bill"),--(C2:C200="x"))*dollar_amount

--
HTH

Bob Phillips

"Galiant" wrote in message
...
I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column
for
that training event rather then 9.75 for example. But we also want to be
able
to add up all training for that individual for the year and see what we
paid
them.

The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a
hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

Update: I adjust the A2-A2000 to A2:28 to fit my sheet, and changed
dolar_value to 10. I get an aswer now, but the answer is 10 it is not
multiplying each instance of "x" times 10.

THanks!

"Galiant" wrote:

I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column for
that training event rather then 9.75 for example. But we also want to be able
to add up all training for that individual for the year and see what we paid
them.

The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

Ok, I think I am getting there... I got it to work with one argument but when
I try to use to arguments I get a "0" values

I have column it setup like this
Member name | Week 1
x
x
x
x
x
d
d
x
d
x
x

Now I want it to tell me how many instances of x and D so I modified it like
so:
=SUMPRODUCT(--(C2:C29="d"),--(C2:C29="x"))*1

THis is returning a "0"

Not sure what is wrong. So to recap, I want it to tell me how many times "x"
occurs and then "d" occurs.




"Galiant" wrote:

Update: I adjust the A2-A2000 to A2:28 to fit my sheet, and changed
dolar_value to 10. I get an aswer now, but the answer is 10 it is not
multiplying each instance of "x" times 10.

THanks!

"Galiant" wrote:

I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column for
that training event rather then 9.75 for example. But we also want to be able
to add up all training for that individual for the year and see what we paid
them.

The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

Hi

The reason Bob used the sumproduct formula along with the A2:A200="Bill"
argument is that he inferred that you wanted to count the number of
x's per person eg Bill. If you just want to count the total number of
x's as well as the total number of d's then you could use:
=COUNTIF(A2:A28,"=x")+COUNTIF(A2:A28,"=d")
and to multiply that by the dollar value
=(COUNTIF(A2:A28,"=x")+COUNTIF(A2:A28,"=d"))*10

Or if you wanted to stick with the sumproduct then
=SUMPRODUCT((A2:A28="x")+(A2:A28="d"))*10

Hope this helps
Rowan

Galiant wrote:
Ok, I think I am getting there... I got it to work with one argument but when
I try to use to arguments I get a "0" values

I have column it setup like this
Member name | Week 1
x
x
x
x
x
d
d
x
d
x
x

Now I want it to tell me how many instances of x and D so I modified it like
so:
=SUMPRODUCT(--(C2:C29="d"),--(C2:C29="x"))*1

THis is returning a "0"

Not sure what is wrong. So to recap, I want it to tell me how many times "x"
occurs and then "d" occurs.




"Galiant" wrote:


Update: I adjust the A2-A2000 to A2:28 to fit my sheet, and changed
dolar_value to 10. I get an aswer now, but the answer is 10 it is not
multiplying each instance of "x" times 10.

THanks!

"Galiant" wrote:


I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column for
that training event rather then 9.75 for example. But we also want to be able
to add up all training for that individual for the year and see what we paid
them.

The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

I also tested against Bill because you said you wanted it for each member.
Bill was just an example name.

--
HTH

Bob Phillips

"Rowan" wrote in message
...
Hi

The reason Bob used the sumproduct formula along with the A2:A200="Bill"
argument is that he inferred that you wanted to count the number of
x's per person eg Bill. If you just want to count the total number of
x's as well as the total number of d's then you could use:
=COUNTIF(A2:A28,"=x")+COUNTIF(A2:A28,"=d")
and to multiply that by the dollar value
=(COUNTIF(A2:A28,"=x")+COUNTIF(A2:A28,"=d"))*10

Or if you wanted to stick with the sumproduct then
=SUMPRODUCT((A2:A28="x")+(A2:A28="d"))*10

Hope this helps
Rowan

Galiant wrote:
Ok, I think I am getting there... I got it to work with one argument but
when
I try to use to arguments I get a "0" values

I have column it setup like this
Member name | Week 1
x
x
x
x
x
d
d
x
d
x
x

Now I want it to tell me how many instances of x and D so I modified it
like
so:
=SUMPRODUCT(--(C2:C29="d"),--(C2:C29="x"))*1

THis is returning a "0"

Not sure what is wrong. So to recap, I want it to tell me how many times
"x"
occurs and then "d" occurs.




"Galiant" wrote:


Update: I adjust the A2-A2000 to A2:28 to fit my sheet, and changed
dolar_value to 10. I get an aswer now, but the answer is 10 it is not
multiplying each instance of "x" times 10.

THanks!

"Galiant" wrote:


I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column
for
that training event rather then 9.75 for example. But we also want to
be able
to add up all training for that individual for the year and see what we
paid
them.

The idea is we have a sheet that shows us checks of who attended and
who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value.
For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each
member
for each "x" and see how much money they recieved. I.E. the "x" is a
hidden
value maybe that when I add up all the "x"s for a member it will give
me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

Thanks, Rowan and Bob

I got it working and it works beautifully!

Thanks again!


"Bob Phillips" wrote:

I also tested against Bill because you said you wanted it for each member.
Bill was just an example name.

--
HTH

Bob Phillips

"Rowan" wrote in message
...
Hi

The reason Bob used the sumproduct formula along with the A2:A200="Bill"
argument is that he inferred that you wanted to count the number of
x's per person eg Bill. If you just want to count the total number of
x's as well as the total number of d's then you could use:
=COUNTIF(A2:A28,"=x")+COUNTIF(A2:A28,"=d")
and to multiply that by the dollar value
=(COUNTIF(A2:A28,"=x")+COUNTIF(A2:A28,"=d"))*10

Or if you wanted to stick with the sumproduct then
=SUMPRODUCT((A2:A28="x")+(A2:A28="d"))*10

Hope this helps
Rowan

Galiant wrote:
Ok, I think I am getting there... I got it to work with one argument but
when
I try to use to arguments I get a "0" values

I have column it setup like this
Member name | Week 1
x
x
x
x
x
d
d
x
d
x
x

Now I want it to tell me how many instances of x and D so I modified it
like
so:
=SUMPRODUCT(--(C2:C29="d"),--(C2:C29="x"))*1

THis is returning a "0"

Not sure what is wrong. So to recap, I want it to tell me how many times
"x"
occurs and then "d" occurs.




"Galiant" wrote:


Update: I adjust the A2-A2000 to A2:28 to fit my sheet, and changed
dolar_value to 10. I get an aswer now, but the answer is 10 it is not
multiplying each instance of "x" times 10.

THanks!

"Galiant" wrote:


I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column
for
that training event rather then 9.75 for example. But we also want to
be able
to add up all training for that individual for the year and see what we
paid
them.

The idea is we have a sheet that shows us checks of who attended and
who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value.
For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each
member
for each "x" and see how much money they recieved. I.E. the "x" is a
hidden
value maybe that when I add up all the "x"s for a member it will give
me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!

Excellent. A co-operative ;-)

--
HTH

Bob Phillips

"Galiant" wrote in message
...
Thanks, Rowan and Bob

I got it working and it works beautifully!

Thanks again!


"Bob Phillips" wrote:

I also tested against Bill because you said you wanted it for each
member.
Bill was just an example name.

--
HTH

Bob Phillips

"Rowan" wrote in message
...
Hi

The reason Bob used the sumproduct formula along with the
A2:A200="Bill"
argument is that he inferred that you wanted to count the number of
x's per person eg Bill. If you just want to count the total number of
x's as well as the total number of d's then you could use:
=COUNTIF(A2:A28,"=x")+COUNTIF(A2:A28,"=d")
and to multiply that by the dollar value
=(COUNTIF(A2:A28,"=x")+COUNTIF(A2:A28,"=d"))*10

Or if you wanted to stick with the sumproduct then
=SUMPRODUCT((A2:A28="x")+(A2:A28="d"))*10

Hope this helps
Rowan

Galiant wrote:
Ok, I think I am getting there... I got it to work with one argument
but
when
I try to use to arguments I get a "0" values

I have column it setup like this
Member name | Week 1
x
x
x
x
x
d
d
x
d
x
x

Now I want it to tell me how many instances of x and D so I modified
it
like
so:
=SUMPRODUCT(--(C2:C29="d"),--(C2:C29="x"))*1

THis is returning a "0"

Not sure what is wrong. So to recap, I want it to tell me how many
times
"x"
occurs and then "d" occurs.




"Galiant" wrote:


Update: I adjust the A2-A2000 to A2:28 to fit my sheet, and changed
dolar_value to 10. I get an aswer now, but the answer is 10 it is
not
multiplying each instance of "x" times 10.

THanks!

"Galiant" wrote:


I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends
the
training we want to just put an "x" in next to their name in the
column
for
that training event rather then 9.75 for example. But we also want
to
be able
to add up all training for that individual for the year and see
what we
paid
them.

The idea is we have a sheet that shows us checks of who attended
and
who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar
value.
For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each
member
for each "x" and see how much money they recieved. I.E. the "x" is
a
hidden
value maybe that when I add up all the "x"s for a member it will
give
me
total dolar value for all training they attended.

I hope that makes sensce and I don't know a lot about excel.
THanks!