Formula Needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 12 Jun 2006 12:06 GMT

I am looking for a formula that will read (1/2 H) this for a register that i
am building and i need to read if there is a half days holiday as i have one
that reads if there is a holiday witch is =countif(k8:k17,"H") i need this to
link in with that formula so it count up half days holidays

would be greatful if there is anyone that could help...

Cheers

Reply to this Message

Andy - 12 Jun 2006 12:21 GMT

Hi

You could try:
=countif(k8:k17,"H") + (COUNTIF(K8:K17,"1/2 H")/2)

Hope this helps.
Andy.

>I am looking for a formula that will read (1/2 H) this for a register that
>i
[quoted text clipped - 7 lines]
>
> Cheers

Reply to this Message

Bob Phillips - 12 Jun 2006 13:52 GMT

An alternative way is to use H for a full day, h for half days, and use

=SUMPRODUCT(--(ISNUMBER(FIND(LOWER("H"),A1:A10)))/2+
ISNUMBER(FIND(UPPER("H"),A1:A10)))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I am looking for a formula that will read (1/2 H) this for a register that i
> am building and i need to read if there is a half days holiday as i have one
[quoted text clipped - 4 lines]
>
> Cheers

Reply to this Message

Bob Phillips - 12 Jun 2006 13:54 GMT

A better formula is

=SUMPRODUCT(EXACT(A1:A10,{"H","h"})*{1,0.5})

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Reply to this Message