We help IT Professionals succeed at work.

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

215 Views
Last Modified: 2018-09-21
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
Comment
Watch Question

Lee W, MVPTechnology and Business Process Advisor
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
This is what I would do.
=(COUNTA(A1:A1000)-(COUNTIF(A1:A1000, "HD")*0.5))
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Another option:
=COUNTIF(B3:B8,"<>HD")+COUNTIF(B3:B8,"HD")/2
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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.
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

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 Analyst
CERTIFIED EXPERT

Commented:
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 Desk
Top Expert 2016

Author

Commented:
Please see attached. Thank you.
Employee-absence-schedule1_Sample.xlsx
Simple Geek from the '70s
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
Thanks all.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.