If any cell in column B contains X, take number from column C in same row and add them up.

[BurtMacklin]

Hey folks,
I am putting together a personal expense spreadsheet and need help with this one part.

On one sheet I have all my debits listed, date-category-price. On a second sheet I have the totals for each category for that month. Each month will have its own sheet.
I want to take all the debits from a certain month and combine them with their respective category be shown on the given month's sheet.

I want the equation to automatically filter out any entries outside of the target month so that I will be able to select the column as opposed to selecting the specific cells within the month.
This will make for a more automated and "live updating" system since I wont have to change the selected cells every time I enter a new debit.

I have attached a simple example of what I am looking for.

Thank you so very much for you help, I really appreciate it!!!

[jindon]

Try

Please Login or Register  to view this content.

[AlphaFrog]

Try something like this.

A B C D 1 Category Month Total 2 Food cost for October Food 10/1/2013 12 3 Clothes cost for October Clothes 10/1/2013 5

Worksheet Formulas

Cell Formula D2 =SUMIFS(Sheet1!C:C,Sheet1!A:A,">="&C2,Sheet1!A:A,"<"&EDATE(C2,1),Sheet1!B:B,B2) D3 =SUMIFS(Sheet1!C:C,Sheet1!A:A,">="&C3,Sheet1!A:A,"<"&EDATE(C3,1),Sheet1!B:B,B3)

Put a date that is the 1st of the month in column C for the month you want summed. You could set the cell formatting to display only the month if you like.

[Leith Ross]

Hello BurtMacklin,

I have expanded on your posted workbook. The attached workbook contains 13 sheets: 1 for each month and a Summary.

You only need to add the expense name to the summary sheet and drag down the row above it to update the formulas. Whenever the expense is entered, the summary sheet will reflect the change.

[BurtMacklin]

It works, jindon!
This is actually the first time I have every put any sort of code like this into excel so I had to follow an online tutorial. Nonetheless it worked perfectly on the example I provided.

Now that I have organized my actually sheet well enough I tried putting the equation (taken from the cell of the sample sheet after I ran the macro) into my actually document.
This is the equation I am using:
=SUMPRODUCT((MONTH('Expense Log'!B9:B14)=10)*('Expense Log'!D9:D14="Personal Care"),'Expense Log'!E9:E14)

However when I change each it to this:
=SUMPRODUCT((MONTH('Expense Log'!B:B)=10)*('Expense Log'!D:D="Personal Care"),'Expense Log'!E:E)
to include the whole column not just a selection of the column. I get #VALUE!

Making that modification on the example sheet didn't cause any trouble at all so I am not sure what the deal is here.
Do you know what this is happening?

I have attached the actual document so you get a better idea of what is going on, and to answer your question yes, it is a collaboration of a couple different templates from MS

thank you for putting up with my inexperience!

Edit: wow didn't see the last two posts here.
Give me a sec to look them over

[jindon]

Now your sheet is properly set, so no need of vba.