Ask Experts Questions for FREE Help !
    vanz123's Avatar
    vanz123 Posts: 1, Reputation: 1
    New Member

    Feb 2, 2010, 03:01 AM
    Summarize data conditionally across 3 sheets in .xls
    I have a file with 3 sheets. The layout is similar, but each of the sheets can have different rows / common rows. Pl see attachment..

    The 3 sheets are A,B,C and contain cost center information and monthwise numbers. While the columns are the same, the cost centers are not identical across 3 sheets. The CC entries can be same or different. Now, I want to create a 4th sheet with same header information and show for all cost centers, the month-wise calculated values i.e. cells in D will show values at a cost center level (the calc need to be applied even when a cost center is not available in the other sheet; default to 0 when NA)

    Cell values in D = cell values in (A+B)-C

    Attached Files
  1. File Type: xls DataCompare_3sheets_20100201.xls (7.5 KB, 132 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert

    Feb 2, 2010, 10:09 AM

    It's a bit of a monster, but put this formula in B2 of your new D sheet and copy it down and across the whole chart:

    =IF(ISNUMBER(MATCH($A2, A!$A:$A, 0)), INDEX(A!B:B, MATCH($A2, A!$A:$A,0)), 0) +
    IF(ISNUMBER(MATCH($A2, B!$A:$A, 0)), INDEX(B!B:B, MATCH($A2, B!$A:$A, 0)), 0) -
    IF(ISNUMBER(MATCH($A2, 'C'!$A:$A, 0)),INDEX(A!B:B, MATCH($A2, 'C'!$A:$A, 0)), 0)

    It's long, but INDEX/MATCH is one of the most robust functions in Excel, it should stay peppy even on very large datasets.
    Attached Files
  3. File Type: xls DataCompare_3sheets_20100201.xls (26.5 KB, 141 views)

Not your question? Ask your question View similar questions


Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.

Check out some similar questions!

Compare related data stored in two or more excel sheets [ 18 Answers ]

How to compare related data stored in two or more different excel sheets? For example, annual sales figure of a number of companies relating to two OR more years available in different excel sheets? Either by grouping (pooling) the data into a single excel sheet or otherwise?

How to retrive all related data from different sheets according to the user's input? [ 10 Answers ]

Hi there, I want to connect two different excel sheet in the manner as follows: Consider I'm using sheet1 with fields like prod_id & prod_name with their data (1,2,3,4,5) and (a,b,c,d,e) respectively. Now if I enter the prod_id as 3 in sheet2, then I need all the other fields related to...

I want to save my data server (containing .doc, xls, pdf files ) [ 1 Answers ]

I want to save my data server (containing .doc, xls, pdf files ) from worms and viruses, specially from those that creates newfolder or subfolder exe files like, rvhost and svchossst. Many clients have already infected with these viruses, but I want to save just my server to get infected with...

View more questions Search