Making a letter have a value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 25 Sep 2007 16:22 GMT

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

Reply to this Message

Ron Coderre - 25 Sep 2007 16:41 GMT

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)

> Hi, I need to make a letter have a value, so I can then use auto sum to
> then
[quoted text clipped - 6 lines]
>
> Dave

Reply to this Message

Darren Bartrup - 25 Sep 2007 16:46 GMT

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.

> 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
[quoted text clipped - 4 lines]
>
> Dave

Reply to this Message

JE McGimpsey - 25 Sep 2007 16:46 GMT

How about

=COUNTIF(B2:Z2,"H")

?

> 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
[quoted text clipped - 4 lines]
>
> Dave

Reply to this Message

Gord Dibben - 25 Sep 2007 16:56 GMT

Basically the same as your other question.

=COUNTIF(A1:A20,"H")

Gord Dibben MS Excel MVP

>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
[quoted text clipped - 4 lines]
>
>Dave

Reply to this Message

Dave Peterson - 25 Sep 2007 17:16 GMT

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
[quoted text clipped - 4 lines]
>
> Dave

Signature

Dave Peterson

Reply to this Message