Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a range based on value criteria | Excel Discussion (Misc queries) | |||
Summing data based on criteria | Excel Discussion (Misc queries) | |||
Summing based on criteria | Excel Worksheet Functions | |||
Summing quantities based on like criteria? | Excel Discussion (Misc queries) | |||
Summing based on 2 criteria | Excel Discussion (Misc queries) |