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
=YEAR(TODAY())
so that I can then use
=YEAR(TODAY())-1
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 &LT;= Closing_Date &LT;= 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.

Posted by Robert on February 23, 2001 5:43 PM

I've tried the following formula to simply get a count of all transactions for the previous year while having the previous year be relative, but with no luck. I think I have the right idea, just the wrong execution:
=COUNTIF(Closing_Date,AND((VALUE(Closing_Date)>=VALUE(DATE(YEAR(TODAY())-1,1,1))),(VALUE(Closing_Date)&LT;=VALUE(DATE(YEAR(TODAY())-1,12,31))))). This is based on =COUNTIF(range,AND(criteria1, criteria2), but I don't know if that's a valid construction.

Posted by Aladin Akyurek on February 24, 2001 12:18 AM

=COUNTIF(Closing_Date,AND((VALUE(Closing_Date)>=VALUE(DATE(YEAR(TODAY())-1,1,1))),(VALUE(Closing_Date)&LT;=VALUE(DATE(YEAR(TODAY())-1,12,31))))). This is based on =COUNTIF(range,AND(criteria1, criteria2), but I don't know if that's a valid construction.

Robert,

Assume your closing dates in A2:A3000, closing prices in B2:B3000, and commissions in C2:C3000.

Enter

E1 =YEAR(TODAY())-1

Array-enter (d.i. hit control+shift+enter at the same time to enter) the following formula

E2 =SUM((YEAR(A2:A3000)=E1)*B2:B3000)

Enter

F1 =YEAR(TODAY())

Array-enter

F2 =SUM((YEAR(A2:A3000)=E1)*B2:B3000)

To count transactions, array-enter

G2 =SUM(1*(YEAR(A2:A30)=E1))
H2 =SUM(1*(YEAR(A2:A30)=F1))

Aladin

Posted by Dave Hawley on February 24, 2001 2:09 AM

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 =YEAR(TODAY()) so that I can then use =YEAR(TODAY())-1 for a relative reference for the previous year. (01/01/previous_year &LT;= Closing_Date &LT;= 12/31/previous year).

Hi Robert

Supposing your dates are in Column A and your Closing price in Column B.

=SUM(IF(YEAR($A$2:$A$10)=YEAR(TODAY()),$B$2:$B$10))

To sum This years closing prices.

=SUM(IF(YEAR($A$2:$A$10)=YEAR(TODAY())-1,$B$2:$B$10))

For last years closing prices.

These are both array formulas and must be entered with Ctrl+Shift+Enter. I have some more examples and important rules of array formulas on my web site, under the link "Array Formulas"

Dave

OzGrid Business Applications

Posted by Dave Hawley on February 24, 2001 2:12 AM

Robert, you should consider a Pivot table for this, it would be ideal.

Dave

OzGrid Business Applications

Posted by Robert on February 24, 2001 11:54 AM

: 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 : =YEAR(TODAY()) : so that I can then use : =YEAR(TODAY())-1 : for a relative reference for the previous year. : (01/01/previous_year &LT;= Closing_Date &LT;= 12/31/previous year).

Hi Robert These are both array formulas and must be entered with Ctrl+Shift+Enter. I have some more examples and important rules of array formulas on my web site, under the link "Array Formulas" Dave

Thanks for the tip. Although hard-coding the range in the formula works (e.g. $A$2:$A$10), I was hoping to be able to insert a named range. When I try this in your formula (e.g. =SUM(IF(YEAR(Closing_Date)=YEAR(TODAY()),Closing_Price)), it returns "$ - " if I hit Ctrl-Shift-Enter (for an array) or "#VALUE" if I just hit Enter. I am setting up the spreadsheet for a friend and would prefer not to have to hard-code it if possible, since data will be continuously entered (i.e. a "no-maintenance" spreadsheet). Your Pivot Table is a good idea, but I won't be the one operating it, so I'd prefer a formula that works all the time without having to manually update the Pivot Table and without having to give instructions on its use. Is there a different formula (or a different construction of the SUM formula) that will allow named ranges instead of absolute ranges? Many thanks!

Posted by Robert on February 24, 2001 12:54 PM

Thanks for the tip. Although hard-coding the range in the formula works (e.g. $A$2:$A$2000), I was hoping to be able to insert a named range. When I try this in your formula (e.g. =SUM(IF(YEAR(Closing_Date)=E1,Closing_Price)), it returns "$1,425,001" if I hit Enter (which totals the entire column of Closing_Price - interesting in itself since there is no $1 amount in any of the figures for it to return $1,425,001), or "#VALUE" if I hit Ctrl-Shift-Enter. I am setting up the spreadsheet for a friend and would prefer not to have to hard-code it if possible, since data will be continuously entered (i.e. a "no-maintenance" spreadsheet). Is there a different formula (or a different construction of the SUM formula) that will allow named ranges instead of absolute ranges? You also mentioned that the COUNTIF argument won't allow multiple criteria, but could you nest COUNTIF arguments or combine them with AND? Many thanks.

Posted by David Hawley on February 24, 2001 3:15 PM

Hi Robert

You can use named ranges with array formulas, but as I say on my web page ALL ranges in an array must have the same number of Rows/Columns. So the array formula:
=SUM(IF(YEAR(Closing_Date)=YEAR(TODAY())-1,Closing_Price))
Will work providing BOTH named ranges consist of the SAME number of rows.

Dave
OzGrid Business Applications

Posted by Aladin Akyurek on February 24, 2001 4:06 PM

Hi Mike,

You want your friend to expand the range of closing dates, closing prices, and commissions without any hassles. That is, the formulas for summing, counting, etc must still work without any editing of the ranges.

I propose that you try the following scheme:

Still assume that the closing dates occupy cells of A from A2 on, the closing prices cells of B from B2 on, and commissions those of C from C2 on.

In cell D2 type: =ADDRESS(ROW(A2),COLUMN(A2))&":"&ADDRESS(COUNTA(A:A),COLUMN(A2))
In cell D3 type: =ADDRESS(ROW(B2),COLUMN(B2))&":"&ADDRESS(COUNTA(B:B),COLUMN(B2))

In cell D4 type: =ADDRESS(ROW(C2),COLUMN(C2))&":"&ADDRESS(COUNTA(C:C),COLUMN(C2))

In columns A, B, and C should be used only for entering the above of data.

As you'll see in a minute the cells D2, D3, and D4 will function just like named ranges and allow your friend to enter data at will.

In cell E1 enter: =YEAR(TODAY())-1
In cell F1 enter: =YEAR(TODAY())

In cell E2 array-enter: =SUM((YEAR(INDIRECT(D2))=E1)*INDIRECT(D3))
In cell E3 array-enter: =SUM(1*(YEAR(INDIRECT(D2))=E1))
In cell F2 array-enter: =SUM((YEAR(INDIRECT(D2))=F1)*INDIRECT(D3))
In cell F3 array-enter: =SUM(1*(YEAR(INDIRECT(D2))=F1))

Done. Note that the above system of formulas eliminate, as you call it, " hard-coding" the ranges in the formulas.

Additional note. Forget about nested COUNTIFs with AND'ed args.

Aladin