Check back later today..
--
Gary''s Student - gsnu200750


"Geo" wrote:

Before I start, thanks for your patience.
Right! sheet(MAIN) has the data that is entered as follows:
columns
A B C D
Date Unit No Time
12/9/07 Med 20 0:30
12/9/07 Pol 10 0:45
13/9/07 Pol 11 0:25
14/9/07 Med 21 0:35
and so on
Rows start at 11 and go to 20000

Sheet2
Cell H6 = Total Med hours
Cell H9 = Total Med hours that fall under a combined 40 hours of both units.
Cell H11= Total Med hours that fall above a combined 40 hours of both units.
Cell J9 = Hours used by Med as a percentage under 40 hours
Cell J11 = Hours used by Med as a percentage under 40 hours

Cell N6 = Total Pol hours
Cell N9 = Total Pol hours that fall under a combined 40 hours of both units.
Cell N11= Total Pol hours that fall above a combined 40 hours of both units.
Cell P9 = Hours used by Pol as a percentage under 40 hours
Cell P11 = Hours used by Pol as a percentage over 40 hours

Cell H3 = reference time (40 hours)

I'v tried to make informative but simple. I hope this helps.
The idea is to find out the hours that fall under the figure in cell H3 when
the data is inserted by both units and also over the figure in cell H3.

--
Geo


"Gary''s Student" wrote:

I am sure we can come up with a solution. I just need a more complete example.
--
Gary''s Student - gsnu200750


"Geo" wrote:

Thanks very Gary the formulas work.
I was hoping to be able to figure out how to use these formulas, but Im
getting confused.
The table I gave you was a very simple example of the one Im using.
What Im trying to do is keep a running total of the combined units up to the
point the hours reaches 40:00 hours then the total continue on the cell
below. I then extract in percentage usage of hours for both units under 40:00
hours and over as well. But its giving me a head ache!!
Any ideas?
--
Geo


"Gary''s Student" wrote:

We can use SUMPRODUCT(). In D1 thru D4:

=MIN(SUMPRODUCT(--(A2:A7="A"),B2:B7),4)
=MIN(SUMPRODUCT(--(A2:A7="B"),B2:B7),4)
=SUMPRODUCT(--(A2:A7="A"),B2:B7)-D1
=SUMPRODUCT(--(A2:A7="B"),B2:B7)-D2

These are NOT array functions.
--
Gary''s Student - gsnu200750


"Geo" wrote:

Thanks gary,
What changes to the formula if I dont want to enter it as a array formulas?
--
Geo


"Gary''s Student" wrote:

for under 5 hours in D1 and D2 enter:

=MIN(SUM(IF(A2:A7="A",B2:B7)),4)
=MIN(SUM(IF(A2:A7="B",B2:B7)),4)

for over 5 hours in D3 and D4 enter:

=SUM(IF(A2:A7="A",B2:B7))-D1
=SUM(IF(A2:A7="B",B2:B7))-D2

All the above are array formulas installed by CNTRL-SHIFT-ENTER
--
Gary''s Student - gsnu200750


"Geo" wrote:

Hi,
I have a spreadsheet with data from two different units. What I would like
to do is to be able to total, for each unit, figures under a certain number
and totals for above the same number.
Example:

Unit Hours
A 2
A 2
B 3
A 4
B 1
B 1
and so on.....

Totals for: Unit Hours
Under 5 hours A 4
B 4

Over 5 hours A 4
B 1


Hope that helps!
--
Geo