H
Hoyos
I have a spreadsheet that adds hours of two different units (unit1 and
unit2). Under each unit heading I have two cells keeping a total for that
unit. What I would like to do is for Cell1, of each unit, show total figure
up to and including a figure in cell A1 then totals above the figure in A1 to
continue to be summed in cell2.
--
Hoyos
Was this post helpful to you?
Reply Top
JE McGimpsey 9/4/2007 2:08 PM PST
one way:
Cell1: =MIN(SUMIF(...),A1)
Cell2: =MAX(0, SUMIF(...)-Cell1)
Click to show or hide original message or reply text.
Did this post answer the question?
Reply Top
T. Valko 9/4/2007 2:08 PM PST
Totals from where? I think you need to provide more DETAILS and a sample
with the expected results.
--
Biff
Microsoft Excel MVP
Click to show or hide original message or reply text.
Did this post answer the question?
Reply Top
Hoyos 9/4/2007 2:29 PM PST
Here is a sample:
A1= 0:20 (Time)
A B
Unit Time
1 0:25
2 0:10
1 0:10
1 0:20
2 0:10
Total 1:15
In cell G1 Unit1 Total under cell A1
G2 Above cell A1
In cell H1 Unit2 Total under cell A1
H2 Above cell A1
Is it possible for the figures under cell A1. When reaching that figure it
freezes.
--
Hoyos
:
Click to show or hide original message or reply text.
Was this post helpful to you?
Reply Top
Sandy Mann 9/4/2007 4:14 PM PST
Do you mean you want a sum of all the cells of Unit 1 with a value of 0:20
or less and another of cells with a value greater than 0:20?
If so then use:
Unit 1 value 0:20 (in C1) or less:
=SUMPRODUCT((A2:A6=1)*(B2:B6<=C1)*B2:B6)
Unit 1 value over 0:20:
=SUMPRODUCT((A2:A6=1)*(B2:B6>C1)*B2:B6)
Unit 2 value 0:20 or less:
=SUMPRODUCT((A2:A6=2)*(B2:B6<=C1)*B2:B6)
Unit 2 value over 0:20:
=SUMPRODUCT((A2:A6=2)*(B2:B6>C1)*B2:B6)
I assumed that values of exactly 0:20 are counted with the less than 0:20
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to show or hide original message or reply text.
Did this post answer the question?
Reply Top
T. Valko 9/4/2007 7:10 PM PST
OK, still not sure what kind of results you're expecting.
Are these the results you expect:
Unit1 Cell1 - 0:20
Unit1 Cell2 - 0:35
Unit2 Cell1 - 0:20
Unit2 Cell2 - 0:00
If those are the results you're looking for use JE's formulas.
--
Biff
Microsoft Excel MVP
Click to show or hide original message or reply text.
Did this post answer the question?
Reply Top
Hoyos 9/6/2007 12:45 AM PST
Sorry, I am being vague.
What I need is to find out what percentage use of both unit1&2 up to 41:40
hours and in a different cell what percentage use between 41:40 hours to 900
hours.
For billing purposes: Unit1 has a budget of 30% under 41:40 hours and I need
to find out if unit1 goes above 30%.
And above 41:40 hours Unit1 has 25% budget. Unit2 would be the remainder of
the hours.
I hope its made it abit clearer.
unit2). Under each unit heading I have two cells keeping a total for that
unit. What I would like to do is for Cell1, of each unit, show total figure
up to and including a figure in cell A1 then totals above the figure in A1 to
continue to be summed in cell2.
--
Hoyos
Was this post helpful to you?
Reply Top
JE McGimpsey 9/4/2007 2:08 PM PST
one way:
Cell1: =MIN(SUMIF(...),A1)
Cell2: =MAX(0, SUMIF(...)-Cell1)
Click to show or hide original message or reply text.
Did this post answer the question?
Reply Top
T. Valko 9/4/2007 2:08 PM PST
Totals from where? I think you need to provide more DETAILS and a sample
with the expected results.
--
Biff
Microsoft Excel MVP
Click to show or hide original message or reply text.
Did this post answer the question?
Reply Top
Hoyos 9/4/2007 2:29 PM PST
Here is a sample:
A1= 0:20 (Time)
A B
Unit Time
1 0:25
2 0:10
1 0:10
1 0:20
2 0:10
Total 1:15
In cell G1 Unit1 Total under cell A1
G2 Above cell A1
In cell H1 Unit2 Total under cell A1
H2 Above cell A1
Is it possible for the figures under cell A1. When reaching that figure it
freezes.
--
Hoyos
:
Click to show or hide original message or reply text.
Was this post helpful to you?
Reply Top
Sandy Mann 9/4/2007 4:14 PM PST
Do you mean you want a sum of all the cells of Unit 1 with a value of 0:20
or less and another of cells with a value greater than 0:20?
If so then use:
Unit 1 value 0:20 (in C1) or less:
=SUMPRODUCT((A2:A6=1)*(B2:B6<=C1)*B2:B6)
Unit 1 value over 0:20:
=SUMPRODUCT((A2:A6=1)*(B2:B6>C1)*B2:B6)
Unit 2 value 0:20 or less:
=SUMPRODUCT((A2:A6=2)*(B2:B6<=C1)*B2:B6)
Unit 2 value over 0:20:
=SUMPRODUCT((A2:A6=2)*(B2:B6>C1)*B2:B6)
I assumed that values of exactly 0:20 are counted with the less than 0:20
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to show or hide original message or reply text.
Did this post answer the question?
Reply Top
T. Valko 9/4/2007 7:10 PM PST
OK, still not sure what kind of results you're expecting.
Are these the results you expect:
Unit1 Cell1 - 0:20
Unit1 Cell2 - 0:35
Unit2 Cell1 - 0:20
Unit2 Cell2 - 0:00
If those are the results you're looking for use JE's formulas.
--
Biff
Microsoft Excel MVP
Click to show or hide original message or reply text.
Did this post answer the question?
Reply Top
Hoyos 9/6/2007 12:45 AM PST
Sorry, I am being vague.
What I need is to find out what percentage use of both unit1&2 up to 41:40
hours and in a different cell what percentage use between 41:40 hours to 900
hours.
For billing purposes: Unit1 has a budget of 30% under 41:40 hours and I need
to find out if unit1 goes above 30%.
And above 41:40 hours Unit1 has 25% budget. Unit2 would be the remainder of
the hours.
I hope its made it abit clearer.