LOOKUP value based on date and category!!

**Si902** · 07-03-2010, 06:29 PM

Hi
Can anyone help with a function / formula for a home accounting spreadsheet.

Im trying to calculate my monthly spend on each category i.e Car Maintenance / Diesel etc.

I have a running total in a sheet named '2010' and is set out as follows :-

Column A = Date, Column C = Category, Column E = Value.

This lists all purchases

i wish to display sum total of the data for a particular month against the corresponding category in my 'Monthly spend' sheet, i.e the data for Car Maintenance would be in 'Monthly spend'!B3

I've tried VLOOKUP / COUNTIFS / SUMIFS but cannot get to work.

**pike** · 07-03-2010, 08:48 PM

Hi Si902,
A Pivot table will do the trick.

**foxguy** · 07-04-2010, 04:18 PM

Hi Si902;

Here's how I do the same thing.

On Sheet("2010")
F1 : =$C1&Month($A1)
F2 : =$C2&Month($A2)
and copy down the column
Then hide column F (optional)

On Sheet("Monthly Spend")
A1 : =6/1/10
A3 : "Car Maintenance"
B3 : =SumIf(2010!$F:$F,$A3&Month($A$1),2010!$C:$C)
B4 : =SumIf(2010!$F:$F,$A4&Month($A$1),2010!$C:$C)

didn't test this, but you should get the idea.

LOOKUP value based on date and category!!

LinkBack

Thread Tools

Rate This Thread

Display

LOOKUP value based on date and category!!

Re: LOOKUP value based on date and category!!

Re: LOOKUP value based on date and category!!

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions