Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to subtotal based on an if
I have a list of people who are in certain groups, each person pays so much
money and the list is added to daily. I want to know what the totals are for different groups. group amt 1 50 1 25 1 75 subtot for 1 2 10 2 20 subtotal for 2 Any help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to subtotal based on an if
Can you use Data Subtotals? Select your table, then use the menu bar to
select Data then Subtotals. In the dialog, indicate that you want a subtotal at each change in Group, producing the Sum of Amt. If you want it in a separate column as you showed it, enter a formula in C2 (assuming the table is in A1:Bxxx, with labels in row 1): =if(a2=a3,"",sum(b$2:b2)-sum(c$1:c1)). Autofill that formula down through column C. --Bruce "Sandy" wrote: I have a list of people who are in certain groups, each person pays so much money and the list is added to daily. I want to know what the totals are for different groups. group amt 1 50 1 25 1 75 subtot for 1 2 10 2 20 subtotal for 2 Any help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to subtotal based on an if
Certainly a Pivot Table is appropriate:
http://peltiertech.com/Excel/Pivots/pivotstart.htm Or, if the groups stay sorted, you could use Data/Subtotals... Or you could use =SUMIF(A:A,1,B:B) In article , Sandy wrote: I have a list of people who are in certain groups, each person pays so much money and the list is added to daily. I want to know what the totals are for different groups. group amt 1 50 1 25 1 75 subtot for 1 2 10 2 20 subtotal for 2 Any help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to subtotal based on an if
Add this formula in row 1 of an adjacent cell
=IF(A2<A1,SUMIF(A:A,A1,B:B),"") and copy down -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Sandy" wrote in message ... I have a list of people who are in certain groups, each person pays so much money and the list is added to daily. I want to know what the totals are for different groups. group amt 1 50 1 25 1 75 subtot for 1 2 10 2 20 subtotal for 2 Any help |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to subtotal based on an if
You can use the Subtotals functionality. Highlight the range to apply your subtotals to, go to DataSubtotals. Select your layout, click OK and it will apply the subtotals to your range. You would need to expand this range each time new data is entered. You could also have cells for each group at the top of your page and assign it a large range and use SUMPRODUCT to keep a running total as new data is entered. So say in A1:B100 is your data. In D1 put the header Group 1, in D2 use this formula: =SUMPRODUCT((A2:A1000=1)*(B2:B1000)) The increased range in the formula will allow you to capture the changes daily so long as the data does not exceed row 1000. If that is the case, adjust your range. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=519770 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to subtotal based on an if
The Subtotal would work, but the columns are say A and AN
"bpeltzer" wrote: Can you use Data Subtotals? Select your table, then use the menu bar to select Data then Subtotals. In the dialog, indicate that you want a subtotal at each change in Group, producing the Sum of Amt. If you want it in a separate column as you showed it, enter a formula in C2 (assuming the table is in A1:Bxxx, with labels in row 1): =if(a2=a3,"",sum(b$2:b2)-sum(c$1:c1)). Autofill that formula down through column C. --Bruce "Sandy" wrote: I have a list of people who are in certain groups, each person pays so much money and the list is added to daily. I want to know what the totals are for different groups. group amt 1 50 1 25 1 75 subtot for 1 2 10 2 20 subtotal for 2 Any help |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to subtotal based on an if
Thank you I got it to work the way I wanted
"bpeltzer" wrote: Can you use Data Subtotals? Select your table, then use the menu bar to select Data then Subtotals. In the dialog, indicate that you want a subtotal at each change in Group, producing the Sum of Amt. If you want it in a separate column as you showed it, enter a formula in C2 (assuming the table is in A1:Bxxx, with labels in row 1): =if(a2=a3,"",sum(b$2:b2)-sum(c$1:c1)). Autofill that formula down through column C. --Bruce "Sandy" wrote: I have a list of people who are in certain groups, each person pays so much money and the list is added to daily. I want to know what the totals are for different groups. group amt 1 50 1 25 1 75 subtot for 1 2 10 2 20 subtotal for 2 Any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sum column C based on a value in Column A? | Excel Worksheet Functions | |||
subtotal a range of cells on a different worksheet | Excel Worksheet Functions | |||
subtotal - pivot table - or better function | Excel Worksheet Functions | |||
Subtotal Bug in Excel 2003 | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |