sum up the ROW values with ref to COLUMN values!

[via135]

i am having months jan, feb.... to jun in b1:g1 & some fruit names i
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
get the sum of total sales..for any given period. for ex. by enterin
"lemon" in A10 i should get the total sales figure of lemon (ie. tota
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!

via13

 

[Bob Phillips]

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]

thks mr Bob for immediate response!

it works superbly!

via135

 

[Ron Rosenfeld]

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]

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]

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

 

[via135]

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