Excel: Reference a cell value as part of a Sum()
Hi All,
Is it possible to reference a cell value as part of a sum
e.g.
=SUM(A2:B6)
if CELL A1 have the value B6 in it, is there a way to use this?
i.e.
=SUM( A2:(=A1) )
Is it possible to reference a cell value as part of a sum
e.g.
=SUM(A2:B6)
if CELL A1 have the value B6 in it, is there a way to use this?
i.e.
=SUM( A2:(=A1) )
Last Comment
ASKER
Book1.xlsxThe green and yellow increases, but the last column will always be grand total on both. I need to create sum that automatically updates.
Give this a try:
=SUM(INDIRECT("B6:" & K2))
You can use an OFFSET function to create a range to SUM:
=SUM(OFFSET($B$6,0,0,MATCH("Grand Total",A:A,0)-6,MATCH("Grand Total",5:5,0)-2))
Range starts at B6
First MATCH finds row with "Grand Total" in column A and deducts 6 to allow for blank rows above data and row containing "Grand Total". This determines range height (number of rows).
Second MATCH finds column with "Grand Total" in row 5 and deducts 2 to allow for blank column before data and column containing "Grand Total". This determines range width (number of columns).
=SUM(OFFSET($B$6,0,0,MATCH("Grand Total",A:A,0)-6,MATCH("Grand Total",5:5,0)-2))
Range starts at B6
First MATCH finds row with "Grand Total" in column A and deducts 6 to allow for blank rows above data and row containing "Grand Total". This determines range height (number of rows).
Second MATCH finds column with "Grand Total" in row 5 and deducts 2 to allow for blank column before data and column containing "Grand Total". This determines range width (number of columns).
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Awesome thanks!
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
You may not be explaining correctly so attach an example workbook.