Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sandy
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JE McGimpsey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SteveG
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sandy
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sandy
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sum column C based on a value in Column A? El Bee Excel Worksheet Functions 2 February 15th 06 09:31 PM
subtotal a range of cells on a different worksheet cheryl Excel Worksheet Functions 0 November 2nd 05 09:37 PM
subtotal - pivot table - or better function klafert Excel Worksheet Functions 0 June 16th 05 06:12 AM
Subtotal Bug in Excel 2003 GON Excel Discussion (Misc queries) 2 May 10th 05 08:42 PM
Subtotal of Subtotal displays Grand Total in wrong row Thomas Born Excel Worksheet Functions 5 January 6th 05 02:46 PM


All times are GMT +1. The time now is 11:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"