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


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=496051

Try this

=SUM(OFFSET(B2,MATCH(A10,$A$2:$A$7,0)-1,0,1,6))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"via135" wrote in
message ...

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


--
via135
------------------------------------------------------------------------
via135's Profile:
http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=496051

thks mr Bob for immediate response!

it works superbly!

via135




Bob Phillips Wrote:
Try this

=SUM(OFFSET(B2,MATCH(A10,$A$2:$A$7,0)-1,0,1,6))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"via135" wrote
in
message ...

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


--
via135

------------------------------------------------------------------------
via135's Profile:
http://www.excelforum.com/member.php...o&userid=26725
View this thread:
http://www.excelforum.com/showthread...hreadid=496051



--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=496051

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

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

On Sat, 31 Dec 2005 13:38:48 -0600, via135
wrote:


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!


The formula should work using either cell references or text strings.

However, the month names are assumed to be text strings, as you wrote in your
initial post, and NOT Excel dates..

For example, given the following table in A1:G7

Jan Feb Mar Apr May Jun
apple 40 33 31 35 26 27
banana 29 29 34 18 33 40
mango 36 17 22 34 35 31
lemon 19 24 19 28 35 28
orange 16 18 21 37 35 36
grapes 28 18 17 30 22 20


The formula:

=SUMPRODUCT(OFFSET(INDEX($A$1:$G$7,MATCH(
"mango",$A$1:$A$7,0),MATCH("Feb",$A$1:$G$1,0)),,,, 3))

will give a result of 73 == the number of mangos sold for three (3) months
starting in February -- i.e. Feb=17; Mar=22; Apr=34


--ron

yes..mr ron!
the problem was in the month names. as u said when i changed the name
of the months from excel format to text..ur formula clicks well!! thks
very much for your prompt clarification..! just for acadamic interest i
am asking the question..why it is not working when the cells are filled
up with months using autofill series?

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=496051