Extracting Data from a Date Range and Criteria

[balloonfiasco]

Hi team I need your help!!

I am attempting to get a formula working in a snazzy budget that Ive bee working on, but it doesn't seem to be working. I have a list of transactions that are recurring from week to week, and I am attempting to have them sorted and exracted based on a specific date range.

Column A: the date
Column B: the transaction category
Column C: transaction amount

Date category amount
2-jan-15 food 8.00
3-jan-15 bill 1 53.00
3-jan-15 VISA Balance 90.00
4-jan-15 Clothes 110.00
4-jan-15 food 15.00
5-jan-15 coffee 2.00
6-jan-15 bill 2 98.00
6-jan-15 rent 1000.00
7-jan-15 food 5.00
8-jan-15 VISA Balance 200.00


The premise here is that I would like to extract the value of "VISA Balance" from the date range of jan-2 to jan-6. This formula needs to be relatively dynamic as I will be using it for other such transactions, and on varying date ranges on an ever expanding worksheet for 2015. I would be willing to provide the workbook if necessary.

The formula that I'm using and not working is =index(c2:c1000,match(A2&"VISA Balance",if(A2:A1000>=2-jan-15,if(A2:A1000<=6-jan-5)),0))

Any help you can give is great!!

[MarvinP]

Hi balloonfiasco and welcome to the forum,

You problem looks like a Pivot Table problem to me. If you supply a workbook with some data I can show you what I'd do with it. I'd create a pivot table where the Category is the Filter, Amount is Values and Dates are the Rows. Then I'd filter on date range to get the correct dates and then filter out the Visa Balances. Easy Peasy and no formulas needed.

Read
http://www.pivot-table.com/2010/03/0...-date-filters/

[nigelbloomy]

In this formula the start date is in B1, the end date is in D1, the category is in B2. The columns can be expanded down to include as much data as you have. Column A has the date. Column B has the category. Column C has the amount.

Please Login or Register  to view this content.

[balloonfiasco]

Wow I didn't even think of using the SUMIFS formula for this extraction! There will really only ever be one value for each of the things I need extracted, so it does the job perfectly!

Cheers!

[nigelbloomy]

Thanks. I'm glad it works for you.

[Worldtraveller]

In this formula the start date is in B1, the end date is in D1, the category is in B2. The columns can be expanded down to include as much data as you have. Column A has the date. Column B has the category. Column C has the amount.

Please Login or Register  to view this content.

This looks like what I'm working on too. I think I have a simple question based on this: is there a particular format to use to hardcode the date to sort by month? I want to put each month it its own column, so the B1 would be 1/1/2015:1/31/2015, for instance, and just hardcode if for each month? Thanks.