Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Using Sum function dynamically

I am trying to figure out how to adjust the range of cells which are summed based on the value within another cell.

so if I had =sum(a1:f1) but I wanted to have the start and end points be dynamic from a column perspective (the row would remain constant). So based on the value of cell a10 and b10, for instance, the starting and ending column position might change to be B1 and E1 or some subset or expansion of A1:F1.

From some of what I have found I think I use INDIRECT but the examples don't reflect what I am trying to do exactly.

Thanks for any help

David
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Using Sum function dynamically

Thanks for this. What if the values in a10 and b10 were numeric?

As in a10 and b10!was the relative column position
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Using Sum function dynamically

Hi,

Am Fri, 3 Nov 2017 11:33:22 -0700 (PDT) schrieb
:

Thanks for this. What if the values in a10 and b10 were numeric?

As in a10 and b10!was the relative column position


then try:
=SUM(OFFSET($A1,,$A$10-1,,$B$10-$A$10+1))


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Using Sum function dynamically

On Friday, November 3, 2017 at 12:51:54 PM UTC-6, Claus Busch wrote:
Hi,

Am Fri, 3 Nov 2017 11:33:22 -0700 (PDT) schrieb


Thanks for this. What if the values in a10 and b10 were numeric?

As in a10 and b10!was the relative column position


then try:
=SUM(OFFSET($A1,,$A$10-1,,$B$10-$A$10+1))


Regards
Claus B.
--
Windows10
Office 2016


great thanks that will work fine.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add row dynamically Gotroots Excel Programming 15 December 11th 09 01:57 PM
Sum Column above current cell dynamically using function call TopSlice Excel Worksheet Functions 7 September 15th 09 02:03 PM
Function To Dynamically Display File Size MDW Excel Worksheet Functions 1 February 16th 06 10:45 PM
Dynamically pasting function from Visual Basic to Excel cell John Fejsa Excel Programming 0 February 10th 04 12:45 AM


All times are GMT +1. The time now is 02:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"