Sumproduct - Adding columns on an expanding monthly database

[Feiticera]

Dear All,

My first visit to this help board, so hello to all, and thanks in advance for any assistance that can be offered.

The following formula is adding a cell from every other column, essentially to add a monthly budget to give a year to date figure.

=SUMPRODUCT((MOD(COLUMN('B90:I90),2))*(B90:I90)

Each month a new column will be added to update my 'year to date' figure. I have maybe 100 rows like this for various budgets.

Is there a way I can easily update the section highlighted red 'I90' so that it would become 'J90'.

In other words I almost need it to state the selection (B90:X) where x can be defined in a seperate box so i can update it each month and make each formula change automatically.

Surely this is possible?

Many thanks,

Feiti

 

[Feiticera]

To clarify further I have a spreadsheet that contains a budget and a 'last year' figure for each month, therefore 2 columns per month.

On a seperate report spreadsheet, each month when i produce this report I need it to sum the alternate columns seperately for 'budget year to date' and 'last year, year to date'.

It is fairly time consuming to change manually, so am looking for a way it can be updated fairly quickly to reflect the new month.

 

[T. Valko]

Dear All,

My first visit to this help board, so hello to all, and thanks in advance for any assistance that can be offered.

The following formula is adding a cell from every other column, essentially to add a monthly budget to give a year to date figure.

=SUMPRODUCT((MOD(COLUMN('B90:I90),2))*(B90:I90)

Each month a new column will be added to update my 'year to date' figure. I have maybe 100 rows like this for various budgets.

Is there a way I can easily update the section highlighted red 'I90' so that it would become 'J90'.

In other words I almost need it to state the selection (B90:X) where x can be defined in a seperate box so i can update it each month and make each formula change automatically.

Surely this is possible?

Many thanks,

Feiti

Why don't you just make the range bigger to allow for future data addition?

=SUMPRODUCT(--(MOD(COLUMN(B90:X90),2)),B90:X90)

 

[Feiticera]

The data already exists, i have a full year of figures - so each month i need it to pull in only the relevant data.

i.e in June i need it to add Jan-June, in July, Jan-July - so at the moment i have to change it each time manually.

Ideally I would have a box that I could put May or Period 5 in, and it would then use this to select the appropriate range.

The reason i cant use your suggestion is it would pull in all the data, whereas i need a gradually increasing selection from it.

 

[T. Valko]

The data already exists, i have a full year of figures - so each month i need it to pull in only the relevant data.

i.e in June i need it to add Jan-June, in July, Jan-July - so at the moment i have to change it each time manually.

Ideally I would have a box that I could put May or Period 5 in, and it would then use this to select the appropriate range.

The reason i cant use your suggestion is it would pull in all the data, whereas i need a gradually increasing selection from it.

OK...

How do you determine which column will be the last column for the range?

Do you use a number? For example, 8. Starting from column B use the next 8 columns in the calculation. The 8th column happens to be column I.

 

[Feiticera]

No at the moment im just manually selecting the range, so for example I am doing a report for May.

I therefore want to add the budget figures for the 5months from January to May to give me a 'year to date' figure.

Each month however has 2 columns, one with the budget, and one with the figure from last year.

Thats how i got to the current forumula:
=SUMPRODUCT((MOD(COLUMN('B90:I90),2))*(B90:I90)

As i need it to add every other column.

The reason the range is B90-I90 is because thats how many columns there happen to be between Jan and May, ive pretty much literally done a drag box to add the figures across the spreadsheet

Each month however i need to reproduce this report with a different range as i will obviously need to add in an extra month, so im having to manually rewrite the formula in each time..

As I am doing this for about 100 lines of figures it becomes quite a lot

 

[T. Valko]

No at the moment im just manually selecting the range, so for example I am doing a report for May.

I therefore want to add the budget figures for the 5months from January to May to give me a 'year to date' figure.

Each month however has 2 columns, one with the budget, and one with the figure from last year.

Thats how i got to the current forumula:
=SUMPRODUCT((MOD(COLUMN('B90:I90),2))*(B90:I90)

As i need it to add every other column.

The reason the range is B90-I90 is because thats how many columns there happen to be between Jan and May, ive pretty much literally done a drag box to add the figures across the spreadsheet

Each month however i need to reproduce this report with a different range as i will obviously need to add in an extra month, so im having to manually rewrite the formula in each time..

As I am doing this for about 100 lines of figures it becomes quite a lot

Do you have column headers with the month names?

If so, you can enter a variable month name in some cell and then we can make the formula calculate from the 1st column to the column that matches the variable month name.

 

[Feiticera]

<TABLE style="WIDTH: 647pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=857 x:str><COLGROUP><COL style="WIDTH: 162pt; mso-width-source: userset; mso-width-alt: 6144" width=216><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2304" width=81><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 1991" span=8 width=70><TBODY><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 162pt; HEIGHT: 17.25pt; BORDER-TOP: black 2pt double; BORDER-RIGHT: #ece9d8" class=xl73 height=23 width=216> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: black 2pt double; BORDER-RIGHT: black 2pt double" class=xl74 width=81> </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 106pt; BORDER-TOP: black 2pt double; BORDER-RIGHT: black 2pt double; mso-ignore: colspan" class=xl70 width=140 colSpan=2 align=middle>February</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 106pt; BORDER-TOP: black 2pt double; BORDER-RIGHT: black 2pt double; mso-ignore: colspan" class=xl70 width=140 colSpan=2 align=middle>March</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 106pt; BORDER-TOP: black 2pt double; BORDER-RIGHT: black 2pt double; mso-ignore: colspan" class=xl79 width=140 colSpan=2 align=middle>April</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 106pt; BORDER-TOP: black 2pt double; BORDER-RIGHT: black 2pt double; mso-ignore: colspan" class=xl70 width=140 colSpan=2 align=middle>May</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=22> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75> </TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl83 height=22>MAIN SUMMARY</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl71 align=middle>Budget</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75 align=middle>Last Year</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl71 align=middle>Budget</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75 align=middle>Last Year</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl71 align=middle>Budget</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75 align=middle>Last Year</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl71 align=middle>Budget</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl75 align=middle>Last Year</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl76 height=23> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl77> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl81> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl81> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl81> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: black 2pt double; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl81> </TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=22>SALES ( VAT incl.)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl67> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=21>Own Sales</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 2pt double" class=xl67> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right x:num="3">3 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right x:num="10">10 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right x:num="2">2 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right x:num="9">9 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right x:num="3">3 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right x:num="11">11 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 2pt double; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right x:num="2">2 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right x:num="8">8 </TD></TR></TBODY></TABLE>


An excerpt of where im pulling the data from:

So to clarify in March I would need it to add Feb-Budget + Mar-Budget and so on...at the moment each month i am changing the formula for each row so that it adds the required columns. Im trying to find a way to improve the formula so that it could be updated to include the correct range easier...

 

[Feiticera]

Ah yes indeed, your suggestion sounds like exactly what im after

The only potential problem perhaps would be that each column month header actually covers 2 columns...

 

[T. Valko]

Ah yes indeed, your suggestion sounds like exactly what im after

The only potential problem perhaps would be that each column month header actually covers 2 columns...

That's not a problem.

Maybe something like this:

Book1
	B	C	D	E	F	G	H	I
1	February		March		April		May
2	Budget	Last Year	Budget	Last Year	Budget	Last Year	Budget	Last Year
3	3	10	2	9	3	11	2	8
Sheet1

A10 = some month name like March
B10 = Last Year or Budget

Formula entered in C10:

=SUMIF(B2:INDEX(B2:I2,MATCH(A10,B1:I1,0)+1),B10,B3:INDEX(B3:I3,MATCH(A10,B1:I1,0)+1))