dynamic SUM

[cata2200]

Hello

I have 31 columns in a row as days of the actual month.
How I can sum the first 'n' columns in the row, where 'n'=today()-1?

Many thanks for tip.

Catalin

 

[Robert Mika]

How big is your range?
By the !31 columns" do you mean one cel in a column or literraly a whole 31 columns?
Do you want to count those days I assume not sum?

 

[cata2200]

Robert
Nope. I have a list with the first column as 'Trade', second as 'Numbers' and rest of 31 as hours during each day of the month, regardless of Feb, Apr, Jun, etc.
On the 34th column I need to have the sum value of all cells in the row, from day 1 to 'today()-1'.

 

[Aladin Akyurek]

Robert
Nope. I have a list with the first column as 'Trade', second as 'Numbers' and rest of 31 as hours during each day of the month, regardless of Feb, Apr, Jun, etc.
On the 34th column I need to have the sum value of all cells in the row, from day 1 to 'today()-1'.

What is the exact range for the first data record including the trade value?

 

[cata2200]

I need to apologize, but I done the work.
The formula is:
=SUM(INDIRECT(CONCATENATE(ADDRESS(ROW(), 30), ":", ADDRESS(ROW(), 30+DAY($B$3)-3))))
Sorry for keeping you busy, but, after a cigarette and a coffee, far away from computer, I could manage to “invent” this formula.
What is doing: is summing all values, from day 1 of the month up to 2 days before ‘today()’.
The reporting date is entered in $B$3.
I suppose to post a sample, but it will take time to learn this facility.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Many thanks once again for support.

 

[Aladin Akyurek]

I need to apologize, but I done the work.
The formula is:
=SUM(INDIRECT(CONCATENATE(ADDRESS(ROW(), 30), ":", ADDRESS(ROW(), 30+DAY($B$3)-3))))
Sorry for keeping you busy, but, after a cigarette and a coffee, far away from computer, I could manage to “invent” this formula.
What is doing: is summing all values, from day 1 of the month up to 2 days before ‘today()’.
The reporting date is entered in $B$3.
I suppose to post a sample, but it will take time to learn this facility.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Many thanks once again for support.

If this formula is replicated for a significant number of records, the volatility score (read: inefficency) would go up accordingly because of INDIRECT, ADDRESS, and ROW which are all volatile functions.