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]

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.

You must consider the processing costs of having the data the way it now comes in though...

 

[bdom2369]

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

Sorry I just can't get this to work, keep getting errors. Can you put it in the sample document I attached so I can see it work?

 

[XOR LX]

Sorry I just can't get this to work, keep getting errors. Can you put it in the sample document I attached so I can see it work?

I'd prefer it if you re-uploaded the workbook yourself with your attempts at following these instructions.

Regards

 

[bdom2369]

I'd prefer it if you re-uploaded the workbook yourself with your attempts at following these instructions.

Regards

Updated
https://drive.google.com/open?id=0B9YXUQxu5Av0ODg4eXoyUnZmNXM

 

[XOR LX]

Ok.

A couple of things.

Firstly, you haven't followed the instructions that I gave about entering these formulas as array formulas.

Secondly, you've decided on an alternative way of storing your sheet names to that which I gave, which is fine, but requires an amendment in the formula.

If you instead list those sheet names in a horizontal range, e.g. A1:C1, then your existing formula (committed correctly with CSE) will work fine as it is.

If you prefer to have those sheet names listed vertically, as is currently the case, then you will need to replace all references within the formula to:

sheets

with:

TRANSPOSE(sheets)

Edit: ignore the above. Replacing the two instances of:

{1;2;3;4}

with:

{1,2,3,4}

will amount to the same thing, and be more efficient.

Regards

 

[bdom2369]

Maybe I am doing something wrong but I can't get it to work. I did a named range for the sheets which is what I thought you were trying to do. I added transpose and its still not working for me. Also I think the {1;2;3;4} for me is not feasible as the data set will be different for each source and not fixed. Which is way I started with a index match/match. Also an array formula might not be best, I think I am going to go with sum product.

 

[XOR LX]

Maybe I am doing something wrong but I can't get it to work. I did a named range for the sheets which is what I thought you were trying to do.

You did, but, as I said, it was a completely different method to that which I originally gave. I chose to input them directly into Name Manager as a (comma-separated) list; you chose to enter the actual sheet names into some cells in the worksheet.

This is fine, providing you follow the instructions I outlined in my last post. I am not sure how you did not manage to get it to work.

Also I think the {1;2;3;4} for me is not feasible as the data set will be different for each source and not fixed.

But I already stated that this value can be made dynamic, to account for any number of columns. I simply chose to make it simpler to begin with.

Regards

 

[bdom2369]

Thanks for the suggestions, but I can't get your suggestion to work. If you could make it work in the shared excel I put on the google drive and show me what your talking about that would help. If not I think I need to move on to another solution.

 

[XOR LX]

I would, but unfortunately I don't know how to edit and/or upload your document.

Also, I'm a little unsure as to how you have not yet managed to implement my instructions. After all, there were only two of them in total which were required of you.

Regards

 

[Ingolf]

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

@bdom, cause you use ";" in regional settings, use XOR LX excellent formula with small change:

=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}))))

remenber this is array formula

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