Link to home
Start Free TrialLog in
Avatar of Edward Pamias
Edward PamiasFlag 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
Avatar of Lee W, MVP
Lee W, MVP
Flag of United States of America image

This is what I would do.
=(COUNTA(A1:A1000)-(COUNTIF(A1:A1000, "HD")*0.5))
Another option:
=COUNTIF(B3:B8,"<>HD")+COUNTIF(B3:B8,"HD")/2
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.
Avatar of 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.
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.
Please see attached. Thank you.
Employee-absence-schedule1_Sample.xlsx
ASKER CERTIFIED SOLUTION
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks all.