• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine & sum of multiple tables

Hi all,

I have several tables with same size with some data (Tables "Data1","Data2", "Data3" in my example). I want to combine and extract sum data like Table "Result". Please help me.

Thanks.
 

Attachments

  • Example.xlsx
    10 KB · Views: 14
Hi Saeed,

You can use the COUNTIF formula, but you must ensure that the names are all the same. So Dr.James is not the same as Dr. James because of the space between the period and James.
 
Hi All,

Just wondering what happened to earlier option of PT where it was allowing to create PT from multiple sheets?

Regards,
Prasad DN
PS: I know this should go as separate thread, but still thought it make sense to check in reference to this thread.
 
Hi Again,

Alt + d + p gives the PT wizard, where we can use multiple range as data for PT.

@OP
As one time effort you can add all the tables as your data source and make PT. when ever there is increase or decrease in table data the PT on refresh will accomodate the same.

Regards,
Prasad DN
PS: BTW, I am confused if my previous post should be deleted or retained.
 
@prasaddn

No doubt Consolidating multiple range in Pivot is gr8, but if you see the output as required by OP in this case will not be possible through it. I already tried it before posting the suggestion of MACRO.

@Saeed Ghazi Joolaee

1. Will all the tables be on one sheet or multiple sheet?
2. Will the no.s of columns in each table is same or they can vary in future?
3. Do each table has a unique number or it's just a data dump.?

Regards,
 
1. Will all the tables be on one sheet or multiple sheet?
2. Will the no.s of columns in each table is same or they can vary in future?
3. Do each table has a unique number or it's just a data dump.?

answers:
1- All tables are in one sheet by now.
2- Number of Columns and Rows are the same in all tables.
3- By now I had't defined a name for tables.

Actually a consolidated Pivot table can't fulfil my request, too; i.e can't calculate sum of "Sessions" column.

I separated tables only because I want an Auto Sum cell for every page on print. If there is a way that I can have an AutoSum cell under a column like "Sessions" which is Autosums only that printed page, I can combine tables.

Regards.
 
Thanks kchiba,

But is there any other way? something like PivotTable?

Saeed Ghazi Joolaee said:
Actually a consolidated Pivot table can't fulfil my request, too; i.e can't calculate sum of "Sessions" column.

Both above comments are contradictory.

I had given suggestion based on your above comment otherwise we can do the summary in your sample file with EXCEL in-built formulas only.

Can you clarify?

Regards,
 
Thanks Somendra Misra,

I only said "something like PivotTable" not "exactly PivotTable". I'm not professional.

I'm Ok with formulas but my tables' data are very extensive and flexible. If there is a formula that can extract each data (i.e. can find doctors names automatically) and then counts them It is Ok.
 
Back
Top