I need a formula to display .5 instead of 1 for a counta formula.

I need a formula if it sees HD in a cell it counts it as .5 and the other characters are counted as 1.

V= 1
P = 1
S = 1
H= 1
HD = .5
TI = 1
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
This is what I would do.
=(COUNTA(A1:A1000)-(COUNTIF(A1:A1000, "HD")*0.5))
Rob HensonFinance AnalystCommented:
Another option:
Rob HensonFinance AnalystCommented:
Another option to be dynamic:

Set up a list with the value against each option eg:
V      1
P      1
S      1
H      1
HD      0.5
TI      1

Then against your list of values use a lookup to assign each row with a value.

B3 being the text value, J3:K8 the list of values for each option.

You can then use:

Or just:
=SUM(C3:C8)  when text values are in B and lookup in C.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Edward PamiasTeam Lead RRS DeskAuthor Commented:
I just found out its a  Excel Template Sheet they are using called Employee Absence Schedule. They modified the sheet and added half day for one of the absent key types. So I need the formula under Total days modified to reflect the new absent key.
Rob HensonFinance AnalystCommented:
If you can upload the file we can put the relevant formulas in place.

You will need to remove the real names/details as this is a Public site and the data can be shared across the whole site.
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Please see attached. Thank you.
David Johnson, CD, MVPRetiredCommented:
make sure you have the formatting of the cells correct.
January fixed

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thanks all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.