How to SUM data sourced through INDIRECT & MATCH

**JXH** · 08-30-2011, 10:23 PM

Hello everyone. First time poster here and hope I follow the rules ok with my first post...

I have a workbook (attached) which will have a number of tabs containing very similar data namely an ID, Currency and Value (not always in that order). I want to conduct a reconciliation tab which will, for each date, go to the associated data tab and SUM all of the values that MATCH each specified currency.

I'm stuck on the SUM part! I can get it to match and pull out one figure but can't get it to sum each and every occurrence in my range.

I know that I could create a pivot table but as I'll be having a number of source sheets it would be easier (I think!) to name my data ranges and source them through the INDIRECT&MATCH formula.

I'm completely open to any suggestions on how better to source the data and any solutions on summing if they exist.... I'm not a hugely experienced excel user and am just blundering my way through the basics at the moment.

I chose to avoid VLOOKUPs as the format of the source tabs may not always be the same and I just want to be able to drag across my formulae without having to do too much manipulation hence the naming of ranges instead of vlookups.

Thanks to all for reading and in advance for any comments

**MarvinP** · 08-30-2011, 10:37 PM

Hi JXH and welcome to the forum.

I really like Pivot Tables for this problem. I'd keep all the data on the same sheet instead of different sheets and filter the pivot. You can then do monthly totals just as easily.

See the attached.

**JXH** · 08-30-2011, 10:47 PM

Thanks MarvinP.

I should probably have been clearer on the output I need which is basically to have a running account of start value then the trades then the closing value for each day. I've updated the spreadsheet to show more of what I'm after. It really needs to be a running total reconciliation. Which is where a pivot wouldn't be ideal but I could source my data from the pivot tables which may be the best way.

Also with combining the dates there's a good chance the data won't be consistent for each date hence wanting to use the name function etc and keep the date tabs separate (I'll be copying these in from an external source).

Thanks again. JXH

**MarvinP** · 08-30-2011, 11:07 PM

Hi,

I still like Pivots as they have a running totla feature in a second column. See the attached.

Also - if you are puling data from a lot of outside sources have you seen PowerPivots? You need 2010 Excel to use it. http://www.powerpivot.com/

**JXH** · 08-31-2011, 12:17 AM

Thanks. Liking the look of powerpoint but subject to my work having old as Excel so unlikely I'll be on 2010 here anytime soon but at home will bear it in mind.

Have managed to fix my formula I think...

=SUMIF(INDIRECT($A2&"!CCY"),C$1,INDIRECT($A2&"!Value"))

A2 being the cell holding the date (and hence sheet lookup) and C1 the desired currency

...seems to do what I need it to do. Will wait and see what happens when I get all my data in though!

Thanks for your help.