how do I add words, holiday =1, holiday am=0.5

[Guest]

I am trying to set up a holiday spreadsheet for work and I have got it so it
adds up all the words holiday to say how many days each employee has taken
but I need to to add up the holiday AM and holiday PM as 0.5. Does anyone
know the formula to do this?

 

[Bob Phillips]

=SUMPRODUCT(--((EXACT(E1:E20,"hAM"))+(EXACT(E1:E20,"hPM"))))*0.5+COUNTIF(E1:E20,"h")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sandy Mann]

GCC,

Try:

=COUNTA(G1:G10)-SUM(--(RIGHT(G1:G10,2)={"AM","PM"}))/2

Array entered with Ctrl + Shift Enter

or

=COUNTA(G1:G10)-SUMPRODUCT(--(RIGHT(G1:G10,2)={"AM","PM"})/2)

Entered normally

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk