vanz123 Posts: 1, Reputation: 1 New Member #1 Feb 2, 2010, 03:01 AM
Summarize data conditionally across 3 sheets in .xls
Hi,
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

Thanks
vani
Attached Files
1. DataCompare_3sheets_20100201.xls (7.5 KB, 132 views)
2.  JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #2 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. DataCompare_3sheets_20100201.xls (26.5 KB, 141 views)

 Question Tools Search this Question Search this Question: Advanced Search

## 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?

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 ) 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...