Formula to summarize data from certain sheets only

[rizmomin]

Hello:

Please refer to attached file.
I have month data as shown in monthly sheet.
I need to summarize the data in Summary Sheet depending on value in cell A1.
In this example A1 is Mar.
So i need formula in highlighted cell to add value from each month until month shown in cell A1.
In this example, i would need to add Jan,Feb and Mar values.
I have manually added in SUmmary Sheet, cell B3 as shown.

Let me know if you have any questions.
Thanks.

Riz

[jason.b75]

Try

=SUMPRODUCT(SUBTOTAL(9,INDIRECT(TEXT(29*ROW(INDIRECT("1:"&MONTH("1"&$A$1))),"mmm")&"!RC",0)))

[rizmomin]

Hello Jason:

Thanks a lot, it works but difficult to understand the formula for my data use.
What is 9 and 29 is referring to?

My data in Jan,Feb,Mar.....sheet which needs to be added at B633 down.

Let me know if any questions

Riz

[jason.b75]

You can enter that formula in any cell on the summary sheet and it will sum the exact same cell from every sheet from Jan up to the month specified in A1 without making any changes to the formula.

The way I read your question from the example file is that B3 in the summary should total B3 from the other sheets, C5 should total C5 from the other sheets Z9 should total Z9 from the others, etc.

9 is a parameter in subtotal, 1 = average, 2 = count, 9 = sum (there are more which you can see if you look at the help file for subtotal).

29 is just a multiplier used to convert an array of {1,2,3} up to 12 into months. Neither of these should be changed.

[rizmomin]

Hello Jason:
I am sorry but my situation is different.
Please refer to attatched sheet.
Data is in row 633.
Would appreciate your help to modify the formula to pick up and add data from each sheet from row 633.
Let me know if any questions.
Riz

[jason.b75]

If the data is always going to start in row 633 then try

=SUMPRODUCT(SUBTOTAL(9,INDIRECT(TEXT(29*ROW(INDIRECT("1:"&MONTH("1"&$A$1))),"mmm")&"!R[630]C",0)))

Data in row 633, formula in row 3. 633 - 3 = 630

[rizmomin]

Hello Jason:

Thanks i think this should work.
I will contact you if any other requirement for this tread.
Thanks
Riz

[rizmomin]

Hello jason:

I am sorry, last requirement.
In summary sheet, i wanted to start the data from column C instead of column B.
I inserted blank column but gives wrong result.
Refer to attached sheet.
Please help modfiy

Thanks

Riz

[jason.b75]

So formula in column C gets data from column B in the other sheets?

=SUMPRODUCT(SUBTOTAL(9,INDIRECT(TEXT(29*ROW(INDIRECT("1:"&MONTH("1"&$A$1))),"mmm")&"!R[630]C[-1]",0)))

The last bit of the formula uses R1C1 notation, which (in my opinion) is the easiest way to create relative references when using INDIRECT.

The number in the square brackets after R denotes the number of rows offset from the formula, 0 = same row, -1 = row above, 1 = row below.
The number in the square brackets after C denotes the number of columns offset from the formula, 0 = same column, -1 = 1 column left, 2 = 2 columns right.

So R[630]C[-1] looks to the cell 630 rows down and 1 column left of the formula.

[rizmomin]

Hello jason:

i think i got i, once again thank you so much for your time.
Riz