What formula is best to use (COUNTIF, SUMIF, SUM) in array with dates?
Posted by Robert on February 23, 2001 4:05 PM
I have a workbook of real estate transactions with data similar to below:
Closing Date Closing Price Commission
01/16/2000 $259,000 $7,770
02/03/2000 $189,000 $5,670
02/15/2000 $274,000 $8,220
03/05/2000 $299,000 $8,970
...(more data to fill out 2000)...
01/18/2001 $162,000 $4,860
02/21/2001 $242,000 $7,260
What I want to create is a formula in one cell that totals the Closing_Price for all transactions in the previous year, and a formula in another cell that totals the Closing_Price for all the transactions in the current year in order to make comparisons of year over year activity (and later to make year-to-date over year-to-date activity).
The trick, though, is that I want the year to be relative and not absolute, since I will be constantly adding new transactions and when 2002 rolls around, I don't want to have to rewrite all the formulas.
I have one cell that returns the Current_Year
so that I can then use
for a relative reference for the previous year.
I have tried using the COUNTIF, SUMIF, and SUM functions and can't seem to get one that will isolate a year in a format similar to:
(01/01/previous_year <= Closing_Date <= 12/31/previous year).
The Conditional Sum Wizard won't accept whole columns (e.g. K:K) as a range, and I can't seem to get the right verbage in an array formula for it to accept it. Any clues? Please.