Convert a Letter to number

[ef0rel1]

Would like to Convert a Letters to numbers and total numbers in cell. Example (A1,A2,A3,=letter V), convert to numbers 1's and total in A4=numbers 3. I'm working on time sheet for work. thanks

 

[Michael M]

Hi and Welcome to the board

Sounds like a pretty difficult way to do a timesheet, but I'd suggest posting a data sample.....see the 2nd line of my tag for downloading HTML maker, to post sample data.
At fist guess I'd maybe look at a VLOOKUP table where the letters and their numeric equivalents are stored.

 

[Armando Montes]

Try this:
=COUNTIF(A1:A4,"V")

 

[ef0rel1]

This formula you gave me worked fine but for only a few cell not for the group i have below =COUNTIF(A1:A4,"V") Here is example of what i'm trying to do.
=COUNTIF(B6:H6,N6:T6,Z6:AF6,AL6:AR6,AX6:BD6,BJ6:BP6,"V") Thanks for your help so far.

 

[mvptomlinson]

If none of the cells between your groups will ever have just a V in them, don't create groups like that. Just use the whole row, e.g.

=COUNTIF(B6:BP6,"V")

If that won't work, perhaps:

=SUMPRODUCT((B6:H6="V")+(N6:T6="V")+(Z6:AF6="V")+(AL6:AR6="V")+(AX6:BD6="V")+(BJ6:BP6="V"))

 

[Armando Montes]

Maybe this:
=SUMPRODUCT(--(B6:BP6="V")*1)

 

[ef0rel1]

This formula worked fine Thanks!!

=SUMPRODUCT((B6:H6="V")+(N6:T6="V")+(Z6:AF6="V")+(AL6:AR6="V")+(AX6:BD6="V")+(BJ6:BP6="V"))

Could this formula be modified, say if count goes over 12, either the letter or cell would change to the color red.

Also how would you make a formula that would count only one item in a cell, example 8 * would like to count only the* with the ranges from the above formula.

 

[mvptomlinson]

You can use conditional formatting on the cell with the formula to check if the value is greater than 12, and if so set the font or fill color to red.

As for your last question, please start a new thread.

 

[ef0rel1]

I'm using excel 2010, can you show what formula would look like and how it's added to the above formula, new to this.