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

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

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 <[email protected]>,
Sandy <[email protected]> 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

You can use the Subtotals functionality. Highlight the range to apply your subtotals to, go to Data>Subtotals. 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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

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