Conditionally summing across a varying number of worksheets

I have a stock tracking excel sheet we use at work which tracks units sold, proceeds, basis, gain/loss, ending units etc.

The sheet has a data tab in which all the key information about stocks is input, and the number of stocks information is put in for can vary from 1 to 180 (the largest number I have dealt with).
I have a macro which sorts and formats my data into a format I can use on the individual stock tracking worksheets and another macro to let me make as many copies of the tracking worksheet as I have stocks on the data tab.

I run into problems when I get to the Summary page though.

The summary page lists all the individual stocks in separate rows with their key information for a given year.

Ex

Sheet # Stock Name Units Sold Proceeds Basis Gain/Loss

Where the sheet # corresponds to the name of the worksheet that stocks transaction history is on. I use numbers and they are between two blank sheets called A and Z, so my sheet tabs at the bottom look like this:
Summary A 1 2 3 4 5 6 Z Data

And the numbers vary based on the number of stocks.


Now to ensure that all the transactions are being picked up by the summary and the worksheet tracking the transactions there is a set of check lines on the summary sheet for each column which is where I am running into difficulty.
The check needs to take the totals from all the below rows (which works) , and the totals of all the transactions on each worksheet which makes up the total reported in the row and make sure they match.
ie: Totals From Below Gives a number for each coloumn
Check Gives the sum of all of the transactions on all the worksheets which make up the total reported below in the rows
Difference Totals from Below - Check

My problem is the check line. I cannot figure out how to get it to sum the values across all the numbered worksheets (and it needs to work for a variable number of sheets being present).
As an example of what the line needs to report here is the basis for the units sold total. Units sold is the SUMIFS(E:E,C:C."Sell",AJ:AJ,$A$3) where E,C,and AJ are on the sheets A:Z and $A$3 is on the summary sheet.

I know I don't need to reference the whole column, just a set of cells slightly higher then what i usually find I need but for the sake of a solutions it is easier to write E:E or even just E then E1:E500.


Any help would be greatly appreciated.

Hi Glenn,

I have sheet here with values which make sense in the appropriate cells but none of the underlying formulas, as I cannot share that work. My data is fictional but I have made it similar to the actual data I work with.

Hopefully I have covered all of the above points you mentioned.

Allison

Hi

I did the one in cell C2 but changed all the A6:A8's to M3 (which has that range in it) as my actual workbook can go from A6:A186 and changing that reference in all the formulas would get tedious quickly everytime the workbook changed.
I am however getting a #ref error and I am not sure why, I also got the same error before changing the references to M3.

{=SUMPRODUCT(SUMIFS(INDIRECT(" ' "&M3&" '!E5:E500"),INDIRECT(" ' "&M3&" '!C5:C500"),"Sell",INDIRECT(" ' "&M3&" '!AJ5:AJ500"),$A$3))}

This is what I put in any ideas where I went wrong?

Also I do know about array formulas and used ctrl + shift+enter. I only come seeking help when I have tried every way I now of to approach it and google has failed me.

Hi Glenn,

Sorry I missed your first longer post. Will that work even though I have other sheets in the workbook (Rawdata, Cdn Summary, US Summary, 8621, Tax Factors, etc)? I couldn't leave the formulas in this workbook as it is one of a set which helps give the small company I work for a competitive edge and the capacity to do a larger scale of work then we could otherwise.

oh and only need to summarize the numbered sheets, the other sheets are calculations done on the data to prep it for entry in software.

This is working great right now. Super touchy in the writing for the rest of the columns but when working showing the right results. Thanks so much! I am going to hold off marking this solved until I find out if my boss is happy with the result and requirement of having the summary sheet a table.

Hi guys,

I ran into another related problem on the summary sheet columns. I really should have looked at all the formulas before I asked for help. There are 3 columns the above formula won't work for but since two are almost identical (a difference of a single number) it is really only the 2 problems.

I have an ending units column in the table which gets its value using a vlookup formula and i have a highest balance column which uses a max/maxifs formula to get its value. I am not sure how to go about combining those functions with the sumproduct function to get a summary result for all tabs.

I am attaching the copy of the workbook where you helped me get the sumproduct with sumifs function working and adding in an example vlookup and max/maxifs function to make it easier for you to see what is going on. The column references have been changed to make no sense from a financial stand point but the formulas use the exact format from my work spreadsheet as they are a little more complex then using a single sumif function.

Thank you for any help you can give me.

You do have the understanding of what is going on there, however if you look closer the numbers are based only on transactions in the year in cell A3 on the summary tab. So in D6:F6 there are no values listed since there are no sales in 2016 on the corresponding tab and it only needs to be reported when there is a sale in the year. Anyway I do not need summary formulas for the proceeds, basis, or G/L columns, the original sumif help you gave worked great. I needed it for column g and h (so a summary of ending units and max) which have the formulas for how the values are pulled in them.

On another note, that array formula will not work for column g as the summary as if the year was listed as say 2014 and the information was entered until 2016 it would only give me the 2016 ending units amount. As well testing it on my actual workbook it was out about 700 units. The formulas originally entered in column G have been tested as working for over 200 different stock portfolios showing no errors, so I am hesitant to change them.

Sorry I didn't realize we were having a versioning issue. The formula going down column g is:
I*FERROR(I*FNA(V*L*OOKUP(D*ATE($A$3,12,31),I*NDIRECT(A6&"!"&$A$10),9),0),0

and the formula going down column h to calculate it is:
I*FERROR(IF($A$3<Y*EAR(I*NDIRECT([@[Tab'#]]&"!"&$B$10)),0,I*FNA(M*AX(M*AXIFS(I*NDIRECT([@[Tab'#]]&"!"&$C$10),I*NDIRECT([@[Tab'#]]&"!"&$D$10),$A$3),[@[Ending Units]]),0)),0

I tried to upload however I am getting blocked when I select go advanced. the hopefully compatible formulas for those columns are

G I*FERROR(V*L*OOKUP(D*ATE($A$3,12,31),I*NDIRECT(A6&"!"&$A$10),9),0
H {I*FERROR(I*F($A$3<Y*EAR(I*NDIRECT([@[Tab'#]]&"!"&$B$10)),0,M*AX(M*AX(I*F(I*NDIRECT([@[Tab'#]]&"!"&$D$10)=$A$3,I*NDIRECT([@[Tab'#]]&"!"&$C$10)),[@[Ending Units]]))),0}

My summary problem is about summarizing the vlookup and maxifs functions so a solution for the two 2010 compatible formulas should hopefully work for the incompatible formulas as well?

Thanks

Sorry about the asterisk's in the formulas, it was not letting me post a response

Hi,

Yes it should be. Sorry about that. I made a typo while transferring the formulas over to the example sheet i posted here from my actual sheet which has the year in column ae.