Total under certain number?

[Geo]

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!

 

[Gary''s Student]

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

 

[Geo]

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

 

[Gary''s Student]

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.

 

[Geo]

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?

 

[Gary''s Student]

I am sure we can come up with a solution. I just need a more complete example.

 

[Geo]

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.

 

[Gary''s Student]

Check back later today..

 

[Geo]

Thanks.
To complete the picture. Basically it`s to with flying hours budgets between
Medical and Police. Medical contributes 30% of the costs and the Police the
rest up to 40 hours per month and 25% over a combined 40 hours.
What I'm trying to do is to know what hours and what percentage each use for
the first 40 hours and then over that. The trouble is that not all the hours
used by Medical fall under 40 hours, some will fall over that figure. You
might have a situation when there is no Medical flights under 40 hours and
all of it over. So they should only pay 25% of the running costs.
I have manage to sort some formulas. Using the same cell as my last post:
Cells:
H3 = the reference figure (40)
H9 = =MIN((H3),H7) Total Med under
H11 = =MAX(H7-(H3),0) Total Med over
J9 = =H9/(C18) Percentage over 40
(note C18 is the total of both units)
J11 = =H11/(C18) Percentage under 40
H7 = =C6 Total Med hours

Polioce are the same but cells start with "N" for the hours and "P" for the
percentages

I hope that gives you more of an idea.