storing lettrs in an excel cell to later = a number for a formula

D

diydan48

in cell b3 I want to store the letter H to indicate a holiday has been taken.
I then need the total column at the end of the week to be able to look at the
H and use it as 7.5. Indicating that 7.5hrs of holiday has been used.
 
J

jim

Have you considered using countif * the value of H? For example:

=countif(B2:B500,"H")*7.5

This would count the number of occurrences of the letter "H" and
multiply that number by the total hours expected.

If H isn't alone, you could create a separate set of cells that lists
letter/number pairs. Then use countif for the specific letter times a
vlookup of that same letter to return it's corresponding multiplier.

For example:
Column B contains the values you want to tally
Column Q lists the letters, G,H,I, etc.
Column R lists corresponding numeric values, 5, 7.5 10, etc.

Your summary section is in AA2:AB5 (where AA lists the letters and
BB will list the totals)

Your formula might look something like:

For G: =countif(B2:B500,"G")*vlookup(AA2,Q2:R5,2,false) [so it
first counts how many times "G" appears and then multiplies it against
the corresponding value you created earlier.]

....repeat for H,I, etc.


Jim
 
Top