sorry mr ron!
i am simply getting a zero while using ur formula!
r u referring to give the fruit name & month name as "cell reference"
or simply the "text value" such as "orange", "apple"..and "jan",
"feb"...?
pl clarify!
via135
Ron Rosenfeld Wrote:
On Mon, 26 Dec 2005 11:51:18 -0600, via135
wrote:
i am having months jan, feb.... to jun in b1:g1 & some fruit names in
a2:a7 (say.."orange", "banana", "grapes", "apple", "mango" &
"lemon").
b2:g7 contains the monthwise sales figure for each fruit.
now what i want is, without using the pivot table, in any other cell,
say in A10 by simply entering any fruit name (which is in col A) can
i
get the sum of total sales..for any given period. for ex. by entering
"lemon" in A10 i should get the total sales figure of lemon (ie.
total
of B7 to G7) or total sales figure of lemon for the months jan,feb &
mar (ie. total of B7 to D7) in cell A11
pl help
thks!
via135
If your fruits are in A2:An; and your months in B2:?2; and you have a
defined
range inclusive of this entire table named Tbl (e.g. A1:G7) then:
=SUMPRODUCT(OFFSET(INDEX(Tbl,MATCH(Fruit,$A$1:$A$7 ,0),
MATCH(StartMonth,$A$1:$G$1,0)),,,,NumMonths))
Where Fruit is the cell containing the name of the fruit of interest;
StartMonth is the first month you wish to total
NumMonths is the number of months you wish to total.
--ron
--
via135
------------------------------------------------------------------------
via135's Profile:
http://www.excelforum.com/member.php...o&userid=26725
View this thread:
http://www.excelforum.com/showthread...hreadid=496051