sum up the ROW values with ref to COLUMN values!
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 |
sum up the ROW values with ref to COLUMN values!
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 |
sum up the ROW values with ref to COLUMN values!
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 |
sum up the ROW values with ref to COLUMN values!
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 |
sum up the ROW values with ref to COLUMN values!
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 |
sum up the ROW values with ref to COLUMN values!
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 |
sum up the ROW values with ref to COLUMN values!
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 |
All times are GMT +1. The time now is 03:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com