Need help with Adding Decimals

E

Elaine

I want to count the number of times a specific string occurs in a row. Then
I want to add whole numbers and decimals. Ex:

Cells B1, B4, B5, and B10 contain the letter "W". Each "W" counts as 1 for
a total of 4. Cell B13 contains the string "S.5". I want to this item to
count as a half or .5. The Sum of B1:B13 = 4.5

Problem: I can't get "S.5" to count as .5. I'm using the following formula
to count each occurrence and then give me a total number of all cells
=COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNTIF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8:AF8,"S.5"))

Is there a way to include an automatic .5 without actually subtracting it
from the end of the string? EX:
=COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNTIF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8:AF8,"S.5"))-.5
 
D

Dave F

If S.5 is in A1, =MID(A1,2,2) will yield .5, to which you can apply any
mathematical operation.

Dave
 
E

Elaine

I don't understand. Can you tell me what to put in which cell? I want the
total to appear in cell AK8.
 
D

Dave F

I think I misinterpreted your question, sorry.

Why don't you change the S.5 part to: (COUNTIF(B8:AF8,"S.5")/2, since you're
assigning it the value of .5 (i.e., 1/2).

So you get:
=COUNTIF(B8:AF8,"S")+(COUNTIF(B8:AF8,"DF"))+(COUNTIF(B8:AF8,"C"))+(COUNTIF(B8:AF8,"W"))+(COUNTIF(B8:AF8,"S.5")/2)

Dave
 
E

Elaine

I think it's doing what I need. Such a simple addition to what I find to be
a complicated string. I'm new at Excel so I REALLY appreciate your help!!!
Many thanks!
 
T

Teethless mama

Simplify version

=SUM(COUNTIF(B8:AF8,{"S","DF","C","W"))+COUNTIF(B8:AF8,"S.5)/2
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top