Hi, I need to make a letter have a value, so I can then use auto sum to then
add up. I need the letter H to have the same valve as one, so I can use this
on an attendance sheet, so when some enters H, it will then be counted as
one. I can then see how many days someone has had off.

Thanks

Dave

With attendance codes for one person in a 12x31 cell range (B2:AF13)

This formula counts the number of cells containing only "H":
=COUNTIF(B2:AF13,"H")

Not sure if this applies, but you could count all of the cells that contain
either "H" or "V" with this formula:
=SUM(COUNTIF(B2:AF13,{"H","V"}))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Dave" wrote in message
...
Hi, I need to make a letter have a value, so I can then use auto sum to
then
add up. I need the letter H to have the same valve as one, so I can use
this
on an attendance sheet, so when some enters H, it will then be counted as
one. I can then see how many days someone has had off.

Thanks

Dave

You won't be able to use the autosum, but this will work:

If your range to enter H's in is cells A1:A2 then this formula entered in
cell A3 will convert H's to 1's:

{=SUM(IF(A1:A2="H",1,0))}

Note, this is entered as an array formula so press Ctrl+Shift+Enter instead
of enter (which puts the { and } around the formula).

If you want to put a half day (valued as 0.5) add an extra IF statement

{=SUM(IF(A1:A2="H",1,IF(A1:A2="AH",0.5,0)))}

I'm using "AH" as my backslash button seems to have stopped working :p

Can you rate the post if it's any help please. Ta.

"Dave" wrote:

Hi, I need to make a letter have a value, so I can then use auto sum to then
add up. I need the letter H to have the same valve as one, so I can use this
on an attendance sheet, so when some enters H, it will then be counted as
one. I can then see how many days someone has had off.

Thanks

Dave

How about

=COUNTIF(B2:Z2,"H")

?

In article ,
Dave wrote:

Hi, I need to make a letter have a value, so I can then use auto sum to then
add up. I need the letter H to have the same valve as one, so I can use this
on an attendance sheet, so when some enters H, it will then be counted as
one. I can then see how many days someone has had off.

Thanks

Dave

Basically the same as your other question.

=COUNTIF(A1:A20,"H")


Gord Dibben MS Excel MVP

On Tue, 25 Sep 2007 08:22:02 -0700, Dave wrote:

Hi, I need to make a letter have a value, so I can then use auto sum to then
add up. I need the letter H to have the same valve as one, so I can use this
on an attendance sheet, so when some enters H, it will then be counted as
one. I can then see how many days someone has had off.

Thanks

Dave

And you have more replies at your other post.

Dave wrote:

Hi, I need to make a letter have a value, so I can then use auto sum to then
add up. I need the letter H to have the same valve as one, so I can use this
on an attendance sheet, so when some enters H, it will then be counted as
one. I can then see how many days someone has had off.

Thanks

Dave

--

Dave Peterson