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

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

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

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

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

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

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