Counting accummilation of hours??

[Simon Lloyd]

Hi all,

I have a workbook that currently has a count up sheet for an instance of certain text in a named range, it counts when people have been booked off work, my problem is that rather than booking days off in the future they will be booking either an 8 hour day in one period or a 12 our day in another and have a total 0f 128 hours to book off. I want to be able to count the amount of hours on my count up sheet for text found in the source sheet, so if "Bloggs J" appears in named range "Hols" and is in between cells A1:A20 then its 8 added to the count up but if it appears in named range "Hols" between cells A20:A30 it adds 12 to the count up!?

I can mail you my current workbook so you can see how it all works at the moment.

Any thoughts?

Simon

P.S i currently use =COUNTIF(Hols,"Bloggs J")

[Aaron Howe]

A quick way of doing it would be:

=IF(COUNTIF(Hols1,"Bloggs J"),COUNTIF(Hols1,"Bloggs
J")+8,IF(COUNTIF(Hols2,"Bloggs J"),COUNTIF(Hols1,"Bloggs J")+12,0))

Thereby using a nested IF statement. However, bear in mind that ADDS 8 to
the count, so where J Bloggs appears in Hols1 (Cells 1 to 20), you will have
that one count *and* an additional 8 - therefore a count of 9 overall. Also,
to do this you would have to split the two ranges into different names. This
was just a quick resolution, I'm sure one of the guys here will have a far
more graceful way of achieving what you are looking for.

HTH

"Simon Lloyd" wrote:

>
> Hi all,
>
> I have a workbook that currently has a count up sheet for an instance
> of certain text in a named range, it counts when people have been
> booked off work, my problem is that rather than booking days off in the
> future they will be booking either an 8 hour day in one period or a 12
> our day in another and have a total 0f 128 hours to book off. I want to
> be able to count the amount of hours on my count up sheet for text found
> in the source sheet, so if "Bloggs J" appears in named range "Hols" and
> is in between cells A1:A20 then its 8 added to the count up but if it
> appears in named range "Hols" between cells A20:A30 it adds 12 to the
> count up!?
>
> I can mail you my current workbook so you can see how it all works at
> the moment.
>
> Any thoughts?
>
> Simon
>
> P.S i currently use =COUNTIF(Hols,"Bloggs J")
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=390844
>
>

[Simon Lloyd]

Hi Aaron,

Thanks for the speedy reply,

I tried your solution but it gave me this error in the cell #NAME? i tried modifying it to no avail........am i missing something?

Simon

P.S sorted the #NAME? problem

P.P.S After the initial count of 9 it only increses by one each time regardles if its a 12hr period or 8hr

[Simon Lloyd]

Sorted the only counting by one, but can not get it to also add on the 12hr periods in the second named range!

Can anyone sort this muddle out please?

Simon

=IF(COUNTIF(Hols1,"Bloggs J"),COUNTIF(Hols1,"Bloggs J")*8,AND(COUNTIF(Hols2,"Bloggs J"),COUNTIF(Hols2,"Bloggs J")*12,0))

[Aaron Howe]

Make sure you named your ranges in line with the formula; or if you're using
different names change the formula accordingly Also, make sure the line
breaks this website adds to the text are removed as that sometimes trips me
up...

"Simon Lloyd" wrote:

>
> Hi Aaron,
>
> Thanks for the speedy reply,
>
> I tried your solution but it gave me this error in the cell #NAME? i
> tried modifying it to no avail........am i missing something?
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=390844
>
>

[Toppers]

Simon,
Try this:

=COUNTIF(Hols1,"Bloggs J")*8+COUNTIF(Hols2,"Bloggs J")*12

HTH

"Simon Lloyd" wrote:

>
> Sorted the only counting by one, but can not get it to also add on the
> 12hr periods in the second named range!
>
> Can anyone sort this muddle out please?
>
> Simon
>
> =IF(COUNTIF(Hols1,"Bloggs J"),COUNTIF(Hols1,"Bloggs
> J")*8,AND(COUNTIF(Hols2,"Bloggs J"),COUNTIF(Hols2,"Bloggs J")*12,0))
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=390844
>
>

[Simon Lloyd]

Cheers Toppers that worked a treat!


Simon!

[Aaron Howe]

Ahh sorry didn't realise both 8hrs and 12hrs were an option, that obviously
makes it easier! Thought it was one or the other

"Toppers" wrote:

> Simon,
> Try this:
>
> =COUNTIF(Hols1,"Bloggs J")*8+COUNTIF(Hols2,"Bloggs J")*12
>
> HTH
>
> "Simon Lloyd" wrote:
>
> >
> > Sorted the only counting by one, but can not get it to also add on the
> > 12hr periods in the second named range!
> >
> > Can anyone sort this muddle out please?
> >
> > Simon
> >
> > =IF(COUNTIF(Hols1,"Bloggs J"),COUNTIF(Hols1,"Bloggs
> > J")*8,AND(COUNTIF(Hols2,"Bloggs J"),COUNTIF(Hols2,"Bloggs J")*12,0))
> >
> >
> > --
> > Simon Lloyd
> > ------------------------------------------------------------------------
> > Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> > View this thread: http://www.excelforum.com/showthread...hreadid=390844
> >
> >