Varying Cells Ranges in a Formula, without VBA

Occasional Contributor

Hello. Is there someone to tell me how to Vary Cells Ranges in a Formula, without VBA.

 

For example, I would like to make the Sum of a series of ranges of cells which varies in the formula in function of the column.

 

Like:

  1. Column A, the formula would be "sum(D1:D7)"
  2. Column B, the formula would be "sum(E8:E14)"
  3. ...
  4. Column X, the formula would be "sum(X7n:X7n+6)"
  5. "7n" and "7n+6" for the end of the ranges, because it is for adding a week of data each time.

My idea was to create:

  • on a first line, a range with the varying values of the names of the columns of each range of the sum
  • on a second line, a range with the varying values of the ordinal of the beginning line of each range of the sum
  • on a third line, a range with the varying values of the ordinal of the end line of each range of the sum

I wanted to create formulas like:

  • "sum(concat(A$1;A$2):concat(A$1;A$3))
    • A1 having the name of the column of the sum formula
    • A2 having the ordinal of the first line of the range of cells of the sum formula
    • A3 having the ordinal of the last line of the range of cells of the sum formula
  • that i could copy-paste by pulling the formula across the lines of my spreadsheet

But Excel doesn't accept it so sorry if I can't give You the example in a existing spreadsheet. 

 

If You are sure it is not possible without VBA, would You be Kind Enough to suggest a model of code I could copy-paste and work with? please

 

Regards,

Didier 

 

 

 

 

 

 

Microsoft® Excel® pour Microsoft 365 MSO (Version 2111 Build 16.0.14701.20254) 64 bits

7 Replies

@DidierDeBordeaux Not entirely sure how your data is organised and where you want to add the SUM formulae, but perhaps the attached workbook helps.

 

The first example assumes that you have columns with continuous data from which you want to sum specific (smaller) blocks of data. The other assumes that you have smaller blocks of data moving across columns and down for every week. In the latter case, the formula is simple. For the first, it becomes a bit less straight-forward. See if you can get it to work in your own schedule(s).

@Riny_van_Eekelen Very Thanks for Your Informations. I will study them right now. But I Understand that it was not very Clear for You to know what I Meant or Need! Regards, Didier

Well, I would like to send You a screenshot, or a pdf attachement, but I don't see how to do!
And even don't see where to find the answer. Could tell me please?

@DidierDeBordeaux You wrote "And even don't see where to find the answer." Did you see the file I attached and click in cells A1, B1, G1 and H1?

 

As for sending a screenshot, use the camera symbol (far right of the picture below) above the area where you write your message.

Screenshot 2021-12-28 at 07.21.59.png

If you still don't succeed, send me a direct message (click on my name tag and press Message)

 

 

"You wrote "And even don't see where to find the answer." Did you see the file I attached " ---> Yes I have seen and open it

"and click in cells A1, B1, G1 and H1?" --> I didn't know I could do that, I will try it

"As for sending a screenshot, use the camera symbol (far right of the picture below) above the area where you write your message." ---> I didn't noticed these tools, I guess I need to "open full text editor".

""You wrote "And even don't see where to find the answer." Did you see the file I attached "
---> Yes I have seen and open it"
-----> Well; I realise that I had only open the preview! ^_^ ~~ Now I have opened it in Excel and then Yes Indeed, I have seen your 2 kinds of Formulas ~~ My body is very sleepy right now so I will postpone a Better Study of them but looks it might be what I was looking for ~~ Thanks a Lot for your Nice help, Dear Riny : )