Summary by value

D

dWilson

I have a spreadsheet in which I've been tracking
accounting transactions...date, account # and amount. I'd
like to be able to enter a single function that would
total all the amounts within acount number, without me
having to select a range, summarize, select another range,
summarize, etc.

In pseudo-SQL,
SELECT ALL ROWS
SUM AMOUNT
BY ACCOUNT

Is there a function I'm missing? Is there a third-party
solution (perhaps freeware SQL that can read Excel?)

Thanks,
DWilson
 
N

Norman Harker

Hi DWilson!

Difficult without a bit more data to go on but take a look at SUMIF

Description:
Add the cells specified by a given criteria

Syntax:
=SUMIF(range,criteria,[sum_range])

Example:
=SUMIF(A1:A200,"="&123456,B1:B200)

Looks at A1:A200 and if the account number = 123456 it adds the amount
against that account number that is in column B.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
D

DWilson

I'm thinking SUMIF won't work...I've got about 1500 line
items I'm trying to distill into about 85 accounts.

Right now the data is this:

Date in column 1, amount in column 2, account in column 3,
payee data in cols 4-12.

In a perfect world, I want 85 lines where I have the
account number in column 1, and the total dollar amount
for that account in column 2.

Unless I key my 85 account numbers into 85 separate sumif
commands, that doesn't seem to be very efficient...

Thanks,
d

-----Original Message-----
Hi DWilson!

Difficult without a bit more data to go on but take a look at SUMIF

Description:
Add the cells specified by a given criteria

Syntax:
=SUMIF(range,criteria,[sum_range])

Example:
=SUMIF(A1:A200,"="&123456,B1:B200)

Looks at A1:A200 and if the account number = 123456 it adds the amount
against that account number that is in column B.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
dWilson said:
I have a spreadsheet in which I've been tracking
accounting transactions...date, account # and amount. I'd
like to be able to enter a single function that would
total all the amounts within acount number, without me
having to select a range, summarize, select another range,
summarize, etc.

In pseudo-SQL,
SELECT ALL ROWS
SUM AMOUNT
BY ACCOUNT

Is there a function I'm missing? Is there a third-party
solution (perhaps freeware SQL that can read Excel?)

Thanks,
DWilson
Click to expand...


.
Click to expand...
 
A

Aladin Akyurek

If you're not willing to list or cannot list the account numbers in a
separate vertical range in order to apply the suggested SumIf formula, try
to build a pivot table from your data.

DWilson said:
I'm thinking SUMIF won't work...I've got about 1500 line
items I'm trying to distill into about 85 accounts.

Right now the data is this:

Date in column 1, amount in column 2, account in column 3,
payee data in cols 4-12.

In a perfect world, I want 85 lines where I have the
account number in column 1, and the total dollar amount
for that account in column 2.

Unless I key my 85 account numbers into 85 separate sumif
commands, that doesn't seem to be very efficient...

Thanks,
d

-----Original Message-----
Hi DWilson!

Difficult without a bit more data to go on but take a look at SUMIF

Description:
Add the cells specified by a given criteria

Syntax:
=SUMIF(range,criteria,[sum_range])

Example:
=SUMIF(A1:A200,"="&123456,B1:B200)

Looks at A1:A200 and if the account number = 123456 it adds the amount
against that account number that is in column B.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
dWilson said:
I have a spreadsheet in which I've been tracking
accounting transactions...date, account # and amount. I'd
like to be able to enter a single function that would
total all the amounts within acount number, without me
having to select a range, summarize, select another range,
summarize, etc.

In pseudo-SQL,
SELECT ALL ROWS
SUM AMOUNT
BY ACCOUNT

Is there a function I'm missing? Is there a third-party
solution (perhaps freeware SQL that can read Excel?)

Thanks,
DWilson
Click to expand...


.
Click to expand...
Click to expand...
 
Top