Sum across multiple sheets with 2 way look up

[bdom2369]

I am looking for a way to populate a summary table. I export multiple sources of data, which all have the same information but arranged in a different way. The constant would be the date that I keep on the far left. So what I am looking for is a way to use the date then look up the column header, go across all the sheets and total. I am thinking index match match with a sum product formula. Maybe have to use an indirect. Or vlookup with date, match row 1 for header, then sum across all the sheets. I can't get it to work out yet.

Here is some sample data in a google drive:
https://drive.google.com/folderview?id=0B9YXUQxu5Av0ODg4eXoyUnZmNXM&usp=sharing

Thanks,
Brandon

 

[Aladin Akyurek]

Structure each relevant sheet the same way. And invoke for the processing something like:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!B:B"),INDIRECT("'"&SheetList&"'!A:A"),D2))

where SheetList refers to a range housing the relevant sheets with the same lay-out and D2 to a criterion that must hold for column A.

 

[XOR LX]

If you are unable to make it such that the columns in each sheet represent the same data, then, based on your file, in B2 of the summary tab, array formula**:

=SUM(IF(T(OFFSET(INDIRECT("'"&Sheets&"'!A1"),,{1;2;3;4}))=B$1,SUMIF(INDIRECT("'"&Sheets&"'!A:A"),$A2,OFFSET(INDIRECT("'"&Sheets&"'!A:A"),,{1;2;3;4}))))

and copied across and down as required.

Sheets is defined in Name Manager viz:

Name: Sheets
Refers to: ={"source1","source2","source3"}

(Or whatever happen to be the sheet names in question.)

Note that I used a static array - {1;2;3;4} - to determine the number of columns across which this calculation is performed, representing, effectively, the four columns from B to E inclusive. If you need a more flexible, dynamic set-up then please let me know.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[bdom2369]

Thanks for the quick response! I was wanting to avoid structuring each sheet the same way. Each sheet is a different export and formatted differently. This is why I was using the 2 way look ups. For example check the date find the match in sheet one, check the same date, then find column in sheet 2 etc, etc, then sum them up.

 

[dreid1011]

Thanks for the quick response! I was wanting to avoid structuring each sheet the same way. Each sheet is a different export and formatted differently. This is why I was using the 2 way look ups. For example check the date find the match in sheet one, check the same date, then find column in sheet 2 etc, etc, then sum them up.

In this case, you could do something like this:

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th>impressions</th><th>clicks</th><th>sales</th></tr>
<tr><td>9/2/2015</td><td>5</td><td>10</td><td>8</td></tr>
<tr><td>9/3/2015</td><td>8</td><td>13</td><td>13</td></tr>
<tr><td>9/4/2015</td><td>8</td><td>14</td><td>17</td></tr>
<tr><td>9/5/2015</td><td>7</td><td>12</td><td>14</td></tr>
</table>


=SUM(INDEX(Sheet2!$B$8:$D$11,MATCH($A5,Sheet2!$A$8:$A$11,0),MATCH(D$1,Sheet2!$B$7:$D$7,0)),INDEX(Sheet3!$B$14:$E$17,MATCH($A5,Sheet3!$A$14:$A$17,0),MATCH(D$1,Sheet3!$B$13:$E$13,0)),INDEX(Sheet4!$B$20:$E$23,MATCH($A5,Sheet4!$A$20:$A$23,0),MATCH(D$1,Sheet4!$B$19:$E$19,0)))

Copy across first row in summary table, then down.

 

[bdom2369]

Ok so you are saying sum then index match match for each sheet. Now if I have 10-30 tabs or something that could get to be a mess. Is there anyway to wrap that up in an array? Like look through sheets a to b and put all the source sheets in between the a and b tabs. Or maybe indirect with a list of sheet names?

 

[XOR LX]

What didn't work with my solution, may I ask?

Regards

 

[dreid1011]

Yes, but I wasn't sure how many sheets you had. You could use a hidden sheet to gather all the data via indexes, then sum it all on the summary sheet.

 

[bdom2369]

What didn't work with my solution, may I ask?

Regards

Sorry XOR LX, when i was responding to Aladin, I missed your post. I think you are very close but I don't follow how the formula will look up the values. The number of columns will very with each source, but I am only looking for a few key metrics. So maybe the dynamic set up you were talking about might be what i need.

 

[XOR LX]

Have you tried it on the workbook you provided?

The formula is only static in the number of columns being searched; the maximum such number across all sheets in your workbook was four, so I set it (statically) to operate over that number of columns only.

But that is not to say that the formula I provided does not take into account:

a) The header ("impressions", "clicks", "sales") pertaining to the column in which the formula lies

b) The date pertaining to the row in which the formula lies

both of which it does.

Regards