Calculating Column Total dynamically

[Former Member]

Hi Experts,

I have a Report wherein worksheet contains 2 reports that share an axis. Now, I would like to sum up total of the column of both "Default Report" and "Report001" dynamically. I am only able to achieve this for a single report for e.g. "Default Report" or "Report001" but not for both.

e.g. Value of "Default Report" for column "B" = 50

       Value of "Report001" for column "B" = 50

Sum of both should be shown below under Column "B" dynamically because, when I change the report selection, I can have different number of rows.

Please help

Thanks in advance,

Best Regards,

Venkata

[former_member186338 · Accepted solution]

Create a local member in the top row of Default Report to have a sum of all rows of Default report (you can hide this row).

Then in the bottom row of Report001 create a local member to sum all rows of Report001 + $B$5, where $B$5 - is the local member result of the Default Report (sum of default report).

Vadim

[former_member186338 · Accepted solution]

Sample (Default Report - Yellow):

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thanks a lot for your Support. Its working Perfect but need your inputs again. Please see below;

1. Coulumns E, F, G and H are Local members and due to this the formula is being applied for these columns as well which should not be the case. Please see screen shot below in row number 1679 where you can see values. The Formula should only be applied from Column where you can see 2015.JAN.

    

    

  2. Please see my Local member Formula below. Its working fine but it should not add columns E,F,G and H.

The Formula should only be applicable from Column "I".

Any suggestions please?

Thanks in advance,

Best Regards,

Venkata

[former_member186338 · Accepted solution]

Absolutely easy:

=IF(COLUMN()=9,SUM(EPMALLMEMBERS)+$I$12),"")

[Former Member · Accepted solution]

Hi Vadim,

Thanks but it is giving me the below error. Please see end of Column "E". Also, no calculation happening from column "I"

My Local member formula

Regards,

Venkata

[former_member186338 · Accepted solution]

Ups, extra ")"

The correct formula:

=IF(COLUMN()=9,SUM(EPMALLMEMBERS)+$I$12,"")

[Former Member · Accepted solution]

Hi Vadim,

Thanks again, that worked from Column E to I, but now, the Total from Column "J" and Rest are disappearing and the formula is still referring to I12 for Column J2 and rest. Please see the screen shot and the Local member formula below;

Regards,

Venkata

[former_member186338 · Accepted solution]

Sorry, but it's absolutely simple Excel formula! You can adjust it yourself like:

=IF(COLUMN()>=9,SUM(EPMALLMEMBERS)+E$12,"")

COLUMN() - is the current cell column number

Instead of fixed $I$12 - use E$12 (will be adjusted!)

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Great..Many many thanks. It working perfectly fine now.

Thanks again for your wonderful support.

Regards,

Venkata