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