Hello,

I am trying to develop a formula from the following data sample that will
sum all values belonging to the same account number and period.

Account Period Amount
300000-0000-11-0000-71 1 (238,662)
300000-0000-11-0000-71 1 376
300000-0000-11-0000-71 2 (345,830)
300000-0000-11-0000-71 2 5,757
300000-0000-11-0000-71 2 (49,943)
300000-0000-11-0000-71 3 (25,671)
300000-0000-11-0000-71 3 (48,710)
300000-0000-11-0000-71 3 (305,829)
300000-0000-11-0000-71 3 399
300000-0000-11-0000-71 3 (108,489)

For example, the formula should sum the first two lines since they are the
same account number and same period. Lines 3-5 should be summed, and so
forth. I think it would be a SumIf formula, but I am not sure how to handle
2 criteria (account and period). The data I have is 4000 lines so doing
these formulas manually is not an option.

Any help is much appreciated.

John

haven't tried a formula for you yet, since i am not sure of what your after,
have you tried the subtotal menu option?
select the columns
click data/subtotals
set the first option to at each change in period
and sum the amount column

if the accounts aren't in order, sort the data before.

if you want to automate it, record a macro as you perform each operation.

if you're looking for something else, i am sure someone will post some code

--


Gary


"John M" wrote in message
...
Hello,

I am trying to develop a formula from the following data sample that will
sum all values belonging to the same account number and period.

Account Period Amount
300000-0000-11-0000-71 1 (238,662)
300000-0000-11-0000-71 1 376
300000-0000-11-0000-71 2 (345,830)
300000-0000-11-0000-71 2 5,757
300000-0000-11-0000-71 2 (49,943)
300000-0000-11-0000-71 3 (25,671)
300000-0000-11-0000-71 3 (48,710)
300000-0000-11-0000-71 3 (305,829)
300000-0000-11-0000-71 3 399
300000-0000-11-0000-71 3 (108,489)

For example, the formula should sum the first two lines since they are the
same account number and same period. Lines 3-5 should be summed, and so
forth. I think it would be a SumIf formula, but I am not sure how to
handle
2 criteria (account and period). The data I have is 4000 lines so doing
these formulas manually is not an option.

Any help is much appreciated.

John

I suppose you could fool around with some SUMPRODUCT or SUMIF formulae but
really in this case a pivot table should be used and you will have your
answer in no time at all. The good news is pivot tables are in fact very
easy to use.

If you don't know how to generate a pivot table, here's a link to get you
started:

http://www.windowsdevcenter.com/pub/...le.html?page=1

"John M" wrote in message
...
Hello,

I am trying to develop a formula from the following data sample that will
sum all values belonging to the same account number and period.

Account Period Amount
300000-0000-11-0000-71 1 (238,662)
300000-0000-11-0000-71 1 376
300000-0000-11-0000-71 2 (345,830)
300000-0000-11-0000-71 2 5,757
300000-0000-11-0000-71 2 (49,943)
300000-0000-11-0000-71 3 (25,671)
300000-0000-11-0000-71 3 (48,710)
300000-0000-11-0000-71 3 (305,829)
300000-0000-11-0000-71 3 399
300000-0000-11-0000-71 3 (108,489)

For example, the formula should sum the first two lines since they are the
same account number and same period. Lines 3-5 should be summed, and so
forth. I think it would be a SumIf formula, but I am not sure how to
handle
2 criteria (account and period). The data I have is 4000 lines so doing
these formulas manually is not an option.

Any help is much appreciated.

John