Sum formula to get overall results from different worksheets..

[asyamonique]

Hello,
Is there any easy way to alter the below formula, which will be more dinamic?
Cos I'm having big head ache if the row number changes for column A datas!
Many thanks.

=SUM(JANUARY!G5+'FEBRUARY '!G5+MARCH!G5+'APRIL '!G5+'MAY '!G5+'JUNE '!G5+'JULY '!G5+'AUGUST '!G5+'SEPTEMBER '!G5+OCTOBER!G5+NOVEMBER!G5+'DECEMBER '!G5

 

[asyamonique]

Hi again,
The post was with missing information....
Actually every sheets has datas from column A to K and every worksheets has named by month's.So I have overall 12 worksheets.
I've add another worksheet which named TOTAL..
Every worksheets Column A datas has the unique reference numbers and column G numbers has the related values from the same row...
This below formula shows that the total sum values of 12 worksheets.
But sometimes the row number of reference numbers are changes due to if there are some adds.So I need to alter the formula everytime if there are addings or removings...
Thanks again.

[COLOR=#333333]=SUM(JANUARY!G5+'FEBRUARY '!G5+MARCH!G5+'APRIL '!G5+'MAY '!G5+'JUNE '!G5+'JULY '!G5+'AUGUST '!G5+'SEPTEMBER '!G5+OCTOBER!G5+NOVEMBER!G5+'DECEMBER '!G5[/COLOR]

 

[asyamonique]

I've put worksheet names into A5:A16 and placed on cell I5 the below formula, but still having #REF! error..

=SUMPRODUCT(SUMIF(INDIRECT("'"&A5:A16&"'!D5:D136"),D5,INDIRECT("'"&A5:A16&"'!I5:I136")))

 

[TotallyConfused]

Hello Asyamonique


If I understand your question correctly, you want to sum the contents of cell G5 from all twelve sheets and put the total in cell G5 of your TOTAL worksheet. If this is correct, then try the following formula. I tested it, and then added a row and a number to one of the monthly sheets. The formula in the TOTAL worksheet automatically adjusted itself to account for that extra row.


I hope this is what you want.


=SUM(JANUARY!G5+FEBRUARY!G5+MARCH!G5+APRIL!G5+MAY!G5+JUNE!G5+JULY!G5+AUGUST!G5+SEPTEMBER!G5+OCTOBER!G5+NOVEMBER!G5+DECEMBER!G5)


TotallyConfused

 

[TotallyConfused]

Hello Asyamonique

If I understand your question correctly, you want to sum the contents of cell G5 from all twelve sheets and put the total in cell G5 of your TOTAL worksheet. If this is correct, then try the following formula. I tested it, and then added a row and a number to one of the monthly sheets. The formula in the TOTAL worksheet automatically adjusted itself to account for that extra row.

I hope this is what you want.

=SUM(JANUARY!G5+FEBRUARY!G5+MARCH!G5+APRIL!G5+MAY!G5+JUNE!G5+JULY!G5+AUGUST!G5+SEPTEMBER!G5+OCTOBER!G5+NOVEMBER!G5+DECEMBER!G5)

TotallyConfused

Hello Asyamonique

If you try to copy my formula directly into your TOTAL worksheet, there seems to be a space in the middle of the word, DECEMBER. You will need to correct that. I don't know how it got there, but I didn't notice this error until after I had posted my answer. Sorry about that. Also make sure there are NO SPACES following the month names on the sheet tabs.

TotallyConfused

 

[asyamonique]

Hi,
Please have a check my first question on that post!
The one you are providing is the same formula I posted.
I need different way to calculate cos rows always may change not stable for G5 or others..
Thanks anyway.

 

[Eric W]

I tried your formula from post 3, and it worked fine for me. Check to make sure that your references are correct. You say the reference number is in A of each sheet, and the value is in G, but your formula uses columns D and I. Also check the spelling of all the sheets in your A5:A16 range.

 

[asyamonique]

Hi,
On my other worksheet named TOTAL;
A5:A16 has the sheet names
D5 all the way down has the ref#
I try to put on cell G5 below formula!

=SUMPRODUCT(SUMIF(INDIRECT("'"&A5:A16&"'!d5:d15"),D5,INDIRECT("'"&A5:A16&"'!i5:i15")))

The other 12 worksheets data's are located in below cells!
ref# are located from column B5 all the way down.
Number values located column G5 all the way down.

 

[asyamonique]

=SUMPRODUCT(SUMIF(INDIRECT("'"&A5:A16&"'!b5:b15"),D5,INDIRECT("'"&A5:A16&"'!g5:g15")))

Not working also

 

[TotallyConfused]

Hi,
Please have a check my first question on that post!
The one you are providing is the same formula I posted.
I need different way to calculate cos rows always may change not stable for G5 or others..
Thanks anyway.

Hello Asyamonique

I guess I don't understand what you are trying to do. I thought you wanted to total all the 'G5' numbers from the twelve monthly sheets and put that grand total in your TOTAL sheet. Nowhere in your formula from your posting #1 or #2 does it mention anything about column 'A'. The formula I gave is SIMILAR (but NOT the same) to what you had in posting #1 and #2 , EXCEPT I removed all the single quote marks that surround MOST of your month names. Why are those quote marks there? NOT ALL of your month names have them. What is that 'reference number' you say is in column 'A' (posting #2 ) and what is it for?

What I did was to create individual monthly sheets like you have. In column 'G' of each sheet, I placed some numbers and totaled them in 'G5'. I assume you totaled them with '=SUM(G1:G4)' and didn't use any '$' signs. If you used something like '=SUM(G1:G$4)' then remove the '$'. My formula totals these individual monthly totals from all the 'G5's and places that grand total in 'G5' of the TOTAL sheet. It worked perfectly. Isn't this what you want? To test everything, I then added some rows to the individual monthly sheets and placed more numbers in those added rows. When I looked at the grand total in the TOTAL sheet, it had changed to account for those added numbers. It worked perfectly. My formula will automatically adjust itself, so there is NOTHING you have to do to change it, no matter how many rows you may add to each monthly sheet.

Have you tried my formula?

=SUM(JANUARY!G5+FEBRUARY!G5+MARCH!G5+APRIL!G5+MAY!G5+JUNE!G5+JULY!G5+AUGUST!G5+SEPTEMBER!G5+OCTOBER!G5+NOVEMBER!G5+DECEMBER!G5)

TotallyConfused