can an array formula do this?
Posted by steve adams on May 18, 2001 5:52 AM
I've got 3 columns: one for the date, one for a transaction, & one for cost. Would like to sum a specific type of transaction for a range of dates. Have tried the following formula...it will sum the cost for the whole range, but not a specific type of transaction within the range. (?)
SUM(IF(Date>=36884,IF(Date<=36887,IF(Trans="(type)*",Cost))))
On a side note, will array formulas accept "BETWEEN" for the date range?
Posted by Aladin Akyurek on May 18, 2001 6:15 AM
Assuming that you named DATES the range with date values, TRANSACTIONS the range of transactions, and COSTS the range of cost values, all via the Name Box or via the option Insert|Name|Define.
Array-enter
=SUM((DATES>=A1)*(DATES<=A2)*(TRANSACTIONS=A3)*(COSTS))
where A1 contains the smallest date criterion, A2 the largest date criterion, A3 the type of transaction.
Aladin
=================
Posted by Dave Hawley on May 18, 2001 6:19 AM
Hi Steve
The problem lies with the use of the Wildcard character. try using this:
=SUM(IF(Date>=36884,IF(Date<=36887,IF(LEFT(Trans,6)="(type)",Cost))))
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on May 18, 2001 7:26 AM
You can replace the component
(TRANSACTIONS=A3) in the suggested formula by
(ISNUMBER(SEARCH(A3,TRANSACTIONS)))
to cover the issue related to your Trans="(type)*".
Posted by steve adams on May 18, 2001 8:51 AM
here's the data I tried the formula on...it returned the wrong result...example: want to total the costs for "(eatin out) between the 24th & 29th...the formula returned 0 & should be $11.80
Thanks!
12/28/00 (basic) phone, Sprint 1.39
12/29/00 (basic) phone, SWB 28.43
12/29/00 (card) Capital One 109.95
12/28/00 (eatin out) Bagel 2.06
12/25/00 (eatin out) Pizza Hut 9.74
12/24/00 (food) Dillon's 1.05
12/27/00 (food) Sun Fresh 3.16
12/27/00 (other) Ebaugh's Gifts 8.21
12/24/00 (other, xmas) Dillon's 20.00
12/27/00 cash (J) 20.00
12/26/00 gas (Emp) 11.30
12/28/00 gas (J) 15.65
Posted by Aladin Akyurek on May 18, 2001 10:06 AM
Steve
You have to array-enter the formula. In order to do that, you need to hit CONTROL+SHIFT+ENTER at the same time, not just enter.
Aladin
