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]

I will give it a go at work tomorrow and report back

Many thanks,

Feiti

 

[T. Valko]

I will give it a go at work tomorrow and report back

Many thanks,

Feiti

I'll keep an eye on this thread.

 

[Feiticera]

Hmm I am struggling somewhat to implement it in my spreadsheet, unforunately its stretching my understanding somewhat so im not sure what to change to get it to work.

Any chance you could pull it apart for me so I know which bits to point where? (Unfortunately the report and the data that it refers to are in seperate files, so when i start linking it in the formula looks horrible and very hard to follow!)

 

[T. Valko]

Hmm I am struggling somewhat to implement it in my spreadsheet, unforunately its stretching my understanding somewhat so im not sure what to change to get it to work.

Any chance you could pull it apart for me so I know which bits to point where? (Unfortunately the report and the data that it refers to are in seperate files, so when i start linking it in the formula looks horrible and very hard to follow!)

Well, that complicates things!

If the data is in a separate FILE then the formula I suggested won't work. You should have mentioned that from the start.

So, I guess it's back to square 1. Let me review the thread and see what I can come up with. I'm going to be away for several hours so it may take a while before I reply back.

 

[T. Valko]

That's not a problem.

Maybe something like this:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 47px"><COL style="WIDTH: 64px"><COL style="WIDTH: 47px"><COL style="WIDTH: 64px"><COL style="WIDTH: 47px"><COL style="WIDTH: 64px"><COL style="WIDTH: 47px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center" colSpan=2>February</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center" colSpan=2>March</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center" colSpan=2>April</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center" colSpan=2>May</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Budget</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Last Year</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Budget</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Last Year</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Budget</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Last Year</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Budget</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Last Year</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">8</TD></TR></TBODY></TABLE>


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))

Well, that complicates things!

If the data is in a separate FILE then the formula I suggested won't work. You should have mentioned that from the start.

So, I guess it's back to square 1. Let me review the thread and see what I can come up with. I'm going to be away for several hours so it may take a while before I reply back.

OK, if the data is in a different file then use this formula:

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

Of course, you'll have to add the file name and the associated path (if necesssary).

 

[Feiticera]

Ok After a little testing I think I have been able to get this to work, looks like it should do exactly what i want!

Thank you very much indeed!

Out of interest, just so i understand what I am actually doing here:

-I am creating 2 indexes (havent used this before) is this essentially an array?

-Then I am telling it to look at my 2 variables and use those to specify which part of the index to look at

-The sumproduct asks it to add the result

Can i ask why the second formula works with for a seperate spreadsheet but the first doesnt?

Anyway, curiosity and problem solved, thanks again Valko,

Best wishes,

Feiti

 

[T. Valko]

Ok After a little testing I think I have been able to get this to work, looks like it should do exactly what i want!

Thank you very much indeed!

Out of interest, just so i understand what I am actually doing here:

-I am creating 2 indexes (havent used this before) is this essentially an array?

-Then I am telling it to look at my 2 variables and use those to specify which part of the index to look at

-The sumproduct asks it to add the result

Can i ask why the second formula works with for a seperate spreadsheet but the first doesnt?

Anyway, curiosity and problem solved, thanks again Valko,

Best wishes,

Feiti

It's just that the SUMPRODUCT function will work when referencing closed external files while the SUMIF function will not.

I guess it boils down to the programmers that actually wrote the code for each of those functions. One was "thinking" and the other wasn't!

Thanks for the feedback!