Summing totals on separate worksheet based on 2 criteria
 

Hi all,

Kind of new to excel and trying to make a simple expense tracking workbook.

On one sheet I have a table where transactions are recorded. Transaction
types will be things like utilities, gas, food, groceries, etc. Each
transaction has a date, type, payee, and total.

I want to use a separate sheet to summarize data in the transactions table.
I'd like to be able to see each type of transaction, totaled for current
month and year to date (two separate columns).

Question: how to I get the total for a category (say, utilities) for the
current month? If I can figure that, I'll be on my way. Can it be done by
using formula's in the fx bar? VBA? I have significant access vba
experience, but virtually none in excel. I'm using excel 2007.

Thanks!
CW

Summing totals on separate worksheet based on 2 criteria
 

Checkout using a Pivot Table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm


--
Gary''s Student - gsnu200763


"Cheese_whiz" wrote:

Hi all,

Kind of new to excel and trying to make a simple expense tracking workbook.

On one sheet I have a table where transactions are recorded. Transaction
types will be things like utilities, gas, food, groceries, etc. Each
transaction has a date, type, payee, and total.

I want to use a separate sheet to summarize data in the transactions table.
I'd like to be able to see each type of transaction, totaled for current
month and year to date (two separate columns).

Question: how to I get the total for a category (say, utilities) for the
current month? If I can figure that, I'll be on my way. Can it be done by
using formula's in the fx bar? VBA? I have significant access vba
experience, but virtually none in excel. I'm using excel 2007.

Thanks!
CW

Summing totals on separate worksheet based on 2 criteria
 

On the second sheet, add a list of the utilities in say A2:An

In B2 (current month) add a formula of

=SUMPRODUCT(--(YEAR(Sheet1!$A$1:$A$1000)=YEAR(TODAY()),--(MONTH(Sheet1!$A$1:$A$1000)=MONTH(TODAY()),--(Sheet1!$B$1:$B$1000=$A2),Sheet1!$D$1:$D$1000)

In C2 (YTD) add a formula of

=SUMPRODUCT(--(YEAR(Sheet1!$A$1:$A$1000)=YEAR(TODAY()),--(MONTH(Sheet1!$A$1:$A$1000)<=MONTH(TODAY()),--(Sheet1!$B$1:$B$1000=$A2),Sheet1!$D$1:$D$1000)

and copy down.

These assume the transaction date in column A, transaction type in B, and
amount in D.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Cheese_whiz" wrote in message
...
Hi all,

Kind of new to excel and trying to make a simple expense tracking
workbook.

On one sheet I have a table where transactions are recorded. Transaction
types will be things like utilities, gas, food, groceries, etc. Each
transaction has a date, type, payee, and total.

I want to use a separate sheet to summarize data in the transactions
table.
I'd like to be able to see each type of transaction, totaled for current
month and year to date (two separate columns).

Question: how to I get the total for a category (say, utilities) for the
current month? If I can figure that, I'll be on my way. Can it be done
by
using formula's in the fx bar? VBA? I have significant access vba
experience, but virtually none in excel. I'm using excel 2007.

Thanks!
CW

Summing totals on separate worksheet based on 2 criteria
 

Thanks Gary for the quick reply and helpful link.

I can get the type on the left side of a pivot table (types = rows), and the
amounts for each type in the next column, but I can't figure out how to use
the date in the main table to give me amounts for 'this month' and 'this
year' instead of the 'grand total' I'm getting.

I'll look around that site some more.....I really just glanced...

Thanks again,
CW

"Gary''s Student" wrote:

Checkout using a Pivot Table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm


--
Gary''s Student - gsnu200763


"Cheese_whiz" wrote:

Hi all,

Kind of new to excel and trying to make a simple expense tracking workbook.

On one sheet I have a table where transactions are recorded. Transaction
types will be things like utilities, gas, food, groceries, etc. Each
transaction has a date, type, payee, and total.

I want to use a separate sheet to summarize data in the transactions table.
I'd like to be able to see each type of transaction, totaled for current
month and year to date (two separate columns).

Question: how to I get the total for a category (say, utilities) for the
current month? If I can figure that, I'll be on my way. Can it be done by
using formula's in the fx bar? VBA? I have significant access vba
experience, but virtually none in excel. I'm using excel 2007.

Thanks!
CW

Summing totals on separate worksheet based on 2 criteria
 

Thanks Bob,

I'll give that a go. I'm green enough with excel that learning both your
suggestion and more about pivot tables would be very helpful.

CW

"Bob Phillips" wrote:

On the second sheet, add a list of the utilities in say A2:An

In B2 (current month) add a formula of

=SUMPRODUCT(--(YEAR(Sheet1!$A$1:$A$1000)=YEAR(TODAY()),--(MONTH(Sheet1!$A$1:$A$1000)=MONTH(TODAY()),--(Sheet1!$B$1:$B$1000=$A2),Sheet1!$D$1:$D$1000)

In C2 (YTD) add a formula of

=SUMPRODUCT(--(YEAR(Sheet1!$A$1:$A$1000)=YEAR(TODAY()),--(MONTH(Sheet1!$A$1:$A$1000)<=MONTH(TODAY()),--(Sheet1!$B$1:$B$1000=$A2),Sheet1!$D$1:$D$1000)

and copy down.

These assume the transaction date in column A, transaction type in B, and
amount in D.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Cheese_whiz" wrote in message
...
Hi all,

Kind of new to excel and trying to make a simple expense tracking
workbook.

On one sheet I have a table where transactions are recorded. Transaction
types will be things like utilities, gas, food, groceries, etc. Each
transaction has a date, type, payee, and total.

I want to use a separate sheet to summarize data in the transactions
table.
I'd like to be able to see each type of transaction, totaled for current
month and year to date (two separate columns).

Question: how to I get the total for a category (say, utilities) for the
current month? If I can figure that, I'll be on my way. Can it be done
by
using formula's in the fx bar? VBA? I have significant access vba
experience, but virtually none in excel. I'm using excel 2007.

Thanks!
CW