great advice after slight tweaking ti works grand now for another question:

how can get the repeating clients w/ less than 4 unit to be tallied and then
added only once instead of for each row?

"Tom Ogilvy" wrote:

> If D2 contains your minute as a whole number, then in E2
> =if(D2<4,0,Roundup(D2/15,30))
>
> would give you the number units without having to have a large table.
>
> Drag fill this formula down column E
>
> Use the Sumif Function
>
> =Sumif(A:A,"Client1",D:D)
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Beth" <Beth@discussions.microsoft.com> wrote in message
> news:91810C27-21BC-4A28-8A60-E46A0D3BD4B5@microsoft.com...
> > This is the set-up (military time):
> >
> > 12:00 13:36 96 <- this calculation is the amount of minutes that the time
> > frame creates this is the calculation to get the amount of minutes:
> > =MINUTE(B6)-MINUTE(C6)*(-1)+((HOUR(B6)-HOUR(C6))*(-1))*60 (Next to this
> cell
> > will be the amount of units that the minutes equals see below for ex.)
> >
> > (will start line with)-> client # 12:00 13:36 96 7 <- 96
> > minutes = 7 units
> >
> > so on another sheet there's a table that has ranges the first number is
> the
> > number of units the range is the amount of minutes that satisfy that unit
> > amount ex:
> > 1 5 6 7 8 9 10 11 12 13 14 15 <- range 5-15 = 1 unit
> > 2 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <- range 16-30 = 2 units
> etc
> >
> > this table goes all the way to 75 units so this table is large.
> >
> > So what I am driving at is this:
> > If the # of minutes is > 4, then look up the minute amount in the table
> and
> > return the unit, else the number of minutes is less than 4 return 0)
> > Also at the end of the entries I want to tally up the amount of units that
> > each client # has because usually there are more than one entry per
> client.
> > Ex.
> >
> > 1000 13:00 13:04 4 0
> > 1000 14:05 14:10 5 0 1 <- 1 unit because of two
> > entries...
> >
> > Any suggestions or ideas this is a big one. I have been toying with it but
> > need some sort of guidance. Don't know VB or Excel to this extent. Thanks
> for
> > any suggestions or help that is offered it is greatly appreciated.
> >
> >
> >
> >
> >
> >
> > "Fredrik Wahlgren" wrote:
> >
> > >
> > > "Beth" <Beth@discussions.microsoft.com> wrote in message
> > > news:3DE8EF29-8EF5-43A0-B88C-D000399DB7E6@microsoft.com...
> > > > how can I get and if statement to read if(a value in cell d6 > 4, then
> > > > look-up a value in this table and return the amount hat value equals,
> else
> > > > entry is not greater than 4 and needs to be added to some sort of
> counter
> > > > that would keep track of instances from that id) any ideas? Thanks
> Beth
> > >
> > > I think you need to submit a complete example. What is "this table" and
> > > "instances from that id" ?
> > > /Fredrik
> > >
> > >
> > >
>
>
>