Summary by value

  • Thread starter Thread starter dWilson
  • Start date Start date
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
 
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).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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).
(e-mail address removed)
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...
 
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).
(e-mail address removed)
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...
 
Back
Top