| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total under certain number?
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total under certain number?
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total under certain number?
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total under certain number?
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total under certain number?
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Total number of months. | Excel Worksheet Functions | |||
| Can I total only a % of a number (cell) base on total of all cel | Excel Discussion (Misc queries) | |||
| How do I find the total number of the same number/letter in a row | Excel Discussion (Misc queries) | |||
| a number as a percentage out of a total | Excel Worksheet Functions | |||
| Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions | |||
Hybrid Mode
