Avatar of Edward Pamias
Edward Pamias
Flag for United States of America asked on

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
Microsoft OfficeSpreadsheetsMicrosoft Excel

Avatar of undefined
Last Comment
Edward Pamias

8/22/2022 - Mon
Lee W, MVP

This is what I would do.
=(COUNTA(A1:A1000)-(COUNTIF(A1:A1000, "HD")*0.5))
Rob Henson

Another option:
=COUNTIF(B3:B8,"<>HD")+COUNTIF(B3:B8,"HD")/2
Rob Henson

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.
=VLOOKUP(B3,$J$3:$K$8,2,FALSE)

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

You can then use:
=SUMPRODUCT(B3:C8)

Or just:
=SUM(C3:C8)  when text values are in B and lookup in C.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Edward Pamias

ASKER
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 Henson

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 Pamias

ASKER
Please see attached. Thank you.
Employee-absence-schedule1_Sample.xlsx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
David Johnson, CD

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Edward Pamias

ASKER
Thanks all.