Exracting Column Data using a Date Range

[kazaly]

I am stumped trying to do this.
I have a workbook in excel 07 with several worksheets.
Worksheet data is arranged by date (column 1 is always "Date") and related data columns are 2,3,4...n (all named).

What I am trying to do is set up an area separate to the data where I can input two dates and return the summed value of another data column for that date range, say column 4 "Cost".

An example would be: cell AS2 gets input "13/08/2012", cell AT2 gets input "20/08/2012", summed output of column 4 "Cost" for that date range is output to cell AU2.

If I could do that from a worksheet other than the one containing the data it would be a bonus but I could work around that if too difficult or too much overhead is involved.

My main problem here is that I need to take periodic snapshots of the dataset for different periods of time, hence the need to be able to just input dates rather than an entire formula.

I just hope there's some wizard here who can put me on the right track.

[:) Sixthsense :)]

In AU2 cell

Please Login or Register  to view this content.

[JFR2262]

this formula is for cell 'AU2' to get a sum of the cost in a specific range
=SUMIFS('Cost Column,'Date Column',">="&AS2,'Date Column',"<="&AT2)

you can also make formulas further down the sheet to return detailed values for the given date range

Thank you

[kazaly]

Thanks sixthsense but I can't get it to work.
No errors but it returns a value of 0 but should calculate 61.48
I went through and even made sure all intervening cells had a value ("0")
I'm still stumped ???

[:) Sixthsense :)]

It should work! If possible attach a sample workbook with less data.

[kazaly]

Thanks also JFR2262, but the formula errors at Cost.
I've even named all the ranges from rows 2 to 500 but no go.
Still stuck I'm afraid

[:) Sixthsense :)]

May be your Cost Column Values are not real Numbers, formatted as text.

[kazaly]

Thanks for that Sixthsense, I've attached the workbook.
My initial example was off the top of my head.
The worksheet is 12Costs.
The date column is A
The Goods column M is what I was calculating.
Your formula is in AC2

[kazaly]

No, column Values are formatted as date and accounting.

[:) Sixthsense :)]

The reason is that the Date's are in Column-A is not real date. It is Text Values. Keep your system Date setting in DD/MM/YYYY format. Close the file

Now control panel>Regional and Language Option>Regonal Option>>Customize>>Date>>

Short date DD/MM/YYYY
Long date DD/MM/YYYY

Ok>>OK.

Open your file and place the cursor in Column-A and press Alt+D+E>>Next>>Next>>Finish.

Now check the formula, you should have got the result.

[kazaly]

Sixthsense thank you so much,
I never would have got that if not for you.
You sir are a Star!

[kazaly]

Just as a matter of interest, is it possible to use this formula from a different worksheet?

[:) Sixthsense :)]

Yes, Use Keyboard or Mouse to select the ranges, excel will automatically add the sheet names and the ranges. Don't refer it manually by typing the reference, since it cause error if you don't know how to refer it by including the sheet name.

[kazaly]

Thanks again for your help with this.
I've fiddled with Excel a lot over the years but some of the advanced functions leave me a bit confused.
I don't think I've ever seen the syntax you used in the SUMIFS formula in the help files.
It reminds me very much of Lotus which I always found to be a lot more straightforward than Excel.
Thanks again for all your help!
Cheers
Col