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

Edward Pamias
Edward Pamias used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

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

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

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

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
Top Expert 2016
Commented:
make sure you have the formatting of the cells correct.
January fixed
Employee-absence-schedule1_Sampledw.xlsx
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
Thanks all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial