Making a letter have a value

[Guest]

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

 

[Ron Coderre]

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)

 

[Guest]

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

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

 

[JE McGimpsey]

How about

=COUNTIF(B2:Z2,"H")

?

 

[Gord Dibben]

Basically the same as your other question.

=COUNTIF(A1:A20,"H")


Gord Dibben MS Excel MVP

 

[Dave Peterson]

And you have more replies at your other post.

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