I am having a problem setting up a formula for a spreadsheet. Column A of the
sheet contains several rows of names, columns B-AF are for days of the month
and contain different letters (A, T, L, E, R, B, M, N) each letter is equal
to a value of either 1, 0.5 or 3. I am trying to do a formula that will
recognize what letter is in columns B-AF for each person and add the values
of those letters to give me a total. Can anyone give me an idea of what
formula to use. Vlookup and If are not working so far. Thanks!

On Tue, 4 Mar 2008 12:54:01 -0800, sferguson
wrote:

I am having a problem setting up a formula for a spreadsheet. Column A of the
sheet contains several rows of names, columns B-AF are for days of the month
and contain different letters (A, T, L, E, R, B, M, N) each letter is equal
to a value of either 1, 0.5 or 3. I am trying to do a formula that will
recognize what letter is in columns B-AF for each person and add the values
of those letters to give me a total. Can anyone give me an idea of what
formula to use. Vlookup and If are not working so far. Thanks!


Something like:

=COUNTIF(B2:AF2,"A")*0.5 + COUNTIF(B2:AF2,"T") + COUNTIF(B2:AF2,"L")*3

OR

=SUMPRODUCT((RNG="T")+((RNG="A")*0.5)+((RNG="L")*3 ))

Extend as necessary

--ron

Thanks so much. I didn't even think of using countif.

"Ron Rosenfeld" wrote:

On Tue, 4 Mar 2008 12:54:01 -0800, sferguson
wrote:

I am having a problem setting up a formula for a spreadsheet. Column A of the
sheet contains several rows of names, columns B-AF are for days of the month
and contain different letters (A, T, L, E, R, B, M, N) each letter is equal
to a value of either 1, 0.5 or 3. I am trying to do a formula that will
recognize what letter is in columns B-AF for each person and add the values
of those letters to give me a total. Can anyone give me an idea of what
formula to use. Vlookup and If are not working so far. Thanks!


Something like:

=COUNTIF(B2:AF2,"A")*0.5 + COUNTIF(B2:AF2,"T") + COUNTIF(B2:AF2,"L")*3

OR

=SUMPRODUCT((RNG="T")+((RNG="A")*0.5)+((RNG="L")*3 ))

Extend as necessary

--ron

On Tue, 4 Mar 2008 13:32:00 -0800, sferguson
wrote:

Thanks so much. I didn't even think of using countif.


You're welcome. Glad to help. Thanks for the feedback.
--ron