Solved

Summing numbers from multiple sheets

Posted on 2011-03-25
42
486 Views
Last Modified: 2012-05-11
Hello,

I have an Excel 2007 workbook that has over a hundred tabs (sheets). Within each sheet there is a column, L (Loading Fees). I need the loading fees from each sheet be to totaled for a specific month.

For example, in the attached spreadsheet, I show one tab. Under column L, Load Fees,
there is a list of numbers. On April 1st, I need to find the total of loading fees for the month of March, and I need to do that as the year goes on, month by month. So in May, I will need to sum the total loading fees for April, and in June sum the total fees in May, and so on.

The trick is that I need to have the loading fees summed for ALL the sheets in the workbook per month, and like I said, there are over a hundred.

To complicate things, column L on some sheets in the workbook have nothing to do with Loading Fees, so they cannot be included in the formula's action. Those tabs have 'MC' at the beginning of their tab label to designate them as different sheets not to be included in this formula's action.

I would like this function to be initiated by a button I place on a different sheet within the work book.  Please see attached file and feel free to ask if any questions. Add-Monthly-Fees.xls
0
Comment
Question by:JaseS
  • 21
  • 20
42 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35216603
This code will create a sheet with your total. Let me know if you need help creating a button

Sub adgs()
Dim sht As Worksheet, dbLoadFee As Double


For Each sht In ActiveWorkbook.Worksheets
    If Left(sht.Name, 2) <> "MC" Then
        dbLoadFee = dbLoadFee + Application.WorksheetFunction.Sum(sht.Range("L2:L" & Rows.Count))
    End If
Next
    
Sheets.Add
ActiveSheet.Cells(1, 1) = "Total loading fees"
ActiveSheet.Cells(1, 2) = dbLoadFee
    
End Sub

Open in new window


Thomas
0
 

Author Comment

by:JaseS
ID: 35217455
Error produced: Run time error 1004: Unable to get the Sum property of the Worksheet function class.

Just to be clear: it only tabulates the sum for the month prior or someway to tabulate for the month I want. Most of the time I will want the totals for the month prior as will seek the total for March in April, and for April, in May and so on.

And the monthly totals are determined by the dates in Col K: Date to load
0
 

Author Comment

by:JaseS
ID: 35218570
Also to note, some sheets have no entry except for the column heading, in Col L. Not all sheets have a loading fee yet.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35219994
JaseS, it's not clear from which column you're taking the months, it seems like it could be column G or K.

I am writing a code that will check only column K. BUT there are some cases where column K is empty, those values will not be considered. Your work will change from preparing those numbers, to making sure that column K is well populated.

With that said, give me 10 minutes.
0
 

Author Comment

by:JaseS
ID: 35220052
I guess the thing to check is if K has a value of the month and if so, it will have a value in L. Col G has nothing to do with the calculation
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220060
ok just one question...

is it ok if the final report provides the values for every month in separate rows? like:

january / 2011: 50000
february / 2011: 3000
march / 2011: 1533
.
.
.
0
 

Author Comment

by:JaseS
ID: 35220072
sure, although once I get the totals for the month, I don't need to run them again, but it's okay
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220079
ok... I'll see what's easier to develop !
give me half hour ! :-)
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220261
here you go.
This code is simple, it takes into consideration the rules that you pointed in your question.
The attached file contains the code, but there's no button to run it.
to use it you have to open the file, go to the VBA by pressing Alt+F11, make sure that Excel has your file as the active workbook, and from withing the main subroutine, hit F5.

Please let me know in case youhave any doubts.
Sub SumUpTheValues()
Dim wbk                                 As Workbook
Dim sht                                 As Worksheet
Dim rngRandom                           As Range
Dim arrMonthsValues(1 To 12, 1 To 2)    As Variant
Dim i                                   As Integer
Dim lngColumnHeader                     As Long
Dim strArrayFormula                     As String

    Set wbk = ActiveWorkbook
    
    For i = 1 To 12
        arrMonthsValues(i, 1) = "'" & VBA.Format(VBA.DateSerial(Year(Now), i, 1), "MMM/YY")
    Next
    
    For Each sht In wbk.Worksheets
        If VBA.Left(sht.Name, 2) <> "MC" Then
            lngColumnHeader = fnColumnNumber(sht, "Load Fee")
            If lngColumnHeader <> 0 Then
                Set rngRandom = fnDetermineRandomRange(sht)
                If rngRandom Is Nothing Then
                    Exit Sub
                Else
                    For i = 1 To 12
                        strArrayFormula = "=SUM(NOT((ISBLANK(K2:K" & rngRandom.Row & ")))*(MONTH(K2:K" & rngRandom.Row & ")=" & i & ")*L2:L" & rngRandom.Row & ")"
                        If Not rngRandom Is Nothing Then rngRandom.FormulaArray = strArrayFormula
                          
                        arrMonthsValues(i, 2) = arrMonthsValues(i, 2) + rngRandom.Value
                        
                    Next
                
                End If
                Set rngRandom = Nothing
            End If
            
        End If
    Next
    
    Call DeleteFinalReport(wbk)
    Set sht = wbk.Sheets.Add(Before:=wbk.Sheets(1))
    With sht
        .Range("A1").Value = "MONTH"
        .Range("B1").Value = "VALUE"
        .Range("A1:B1").Font.Bold = True
        .Range(.Cells(2, 1), .Cells(13, 2)).Value = arrMonthsValues
        .Range("B:B").NumberFormat = "#,##0.00"
        .Name = "Final Report"
    End With
    Set sht = Nothing
    Set wbk = Nothing
    
End Sub

Private Function fnColumnNumber(sht As Worksheet, strColumnHeader As String) As Long
On Error Resume Next
    fnColumnNumber = Application.WorksheetFunction.Match(strColumnHeader, sht.Rows(1), 0)
End Function

Private Function fnDetermineRandomRange(sht As Worksheet) As Range
On Error Resume Next
    Set fnDetermineRandomRange = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Cells(1)
End Function

Private Sub DeleteFinalReport(wbk As Workbook)
On Error Resume Next
    With Application
        .DisplayAlerts = False
        wbk.Sheets("Final Report").Delete
        .DisplayAlerts = True
    End With
End Sub

Open in new window

Add-Monthly-Fees.xls
0
 

Author Comment

by:JaseS
ID: 35220316
ok, you're losing me here.
I don't know what you mean by
"to use it you have to open the file, go to the VBA by pressing Alt+F11, make sure that Excel has your file as the active workbook, and from withing the main subroutine, hit F5."

Can't I create a button and insert the code behind it? And if so, where, what page do I put the button?
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220329
Hey Jases... I realized some flaws in the code above, so I improved the example.
Code and file with a button is attached, to make it easier for you.

p.s.: This code will execute similar to SUMIF for each month, only if the sheet does NOT start with MC and if it has a column names Load Fee...

to use it, make sure you have only two workbooks opened, the one attached in this post and the one with all the 100 sheets.

Then click the button and wait 2 seconds.

:) Enjoy !
Sub SumUpTheValues()
Dim wbkEach                             As Workbook
Dim wbk                                 As Workbook
Dim sht                                 As Worksheet
Dim rngRandom                           As Range
Dim arrMonthsValues(1 To 12, 1 To 2)    As Variant
Dim i                                   As Integer
Dim lngColumnHeader                     As Long
Dim strArrayFormula                     As String

    If Application.Workbooks.Count <> 2 Then
        MsgBox "This code was designed to run with only two workbooks open." & vbCrLf & "Please close all other workbooks.", _
               vbCritical + vbOKOnly, _
               "Error"
        Exit Sub
    End If
    
    For Each wbkEach In Application.Workbooks
        If wbkEach.Name <> ThisWorkbook.Name And Not wbkEach.IsAddin Then
            Set wbk = wbkEach
        End If
    Next wbkEach
    
    If wbk Is Nothing Then Exit Sub
    wbk.Activate
    For i = 1 To 12
        arrMonthsValues(i, 1) = "'" & VBA.Format(VBA.DateSerial(Year(Now), i, 1), "MMM/YY")
    Next
    
    For Each sht In wbk.Worksheets
        If VBA.Left(sht.Name, 2) <> "MC" Then
            lngColumnHeader = fnColumnNumber(sht, "Load Fee")
            If lngColumnHeader <> 0 Then
                Set rngRandom = fnDetermineRandomRange(sht)
                If rngRandom Is Nothing Then
                    Exit Sub
                Else
                    For i = 1 To 12
                        strArrayFormula = "=SUM(NOT((ISBLANK(K2:K" & rngRandom.Row & ")))*(MONTH(K2:K" & rngRandom.Row & ")=" & i & ")*L2:L" & rngRandom.Row & ")"
                        If Not rngRandom Is Nothing Then rngRandom.FormulaArray = strArrayFormula
                          
                        arrMonthsValues(i, 2) = arrMonthsValues(i, 2) + rngRandom.Value
                        
                    Next
                
                End If
                rngRandom.Clear
                Set rngRandom = Nothing
            End If
            
        End If
    Next
    
    Call DeleteFinalReport(wbk)
    Set sht = wbk.Sheets.Add(Before:=wbk.Sheets(1))
    With sht
        .Range("A1").Value = "MONTH"
        .Range("B1").Value = "VALUE"
        .Range("A1:B1").Font.Bold = True
        .Range(.Cells(2, 1), .Cells(13, 2)).Value = arrMonthsValues
        .Range("B:B").NumberFormat = "#,##0.00"
        On Error Resume Next
        .Name = "Final Report"
    End With
    Set sht = Nothing
    Set wbk = Nothing
    
End Sub

Private Function fnColumnNumber(sht As Worksheet, strColumnHeader As String) As Long
On Error Resume Next
    fnColumnNumber = Application.WorksheetFunction.Match(strColumnHeader, sht.Rows(1), 0)
End Function

Private Function fnDetermineRandomRange(sht As Worksheet) As Range
On Error Resume Next
    Set fnDetermineRandomRange = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Cells(1)
End Function

Private Sub DeleteFinalReport(wbk As Workbook)
On Error Resume Next
    If wbk.Worksheets.Count > 1 Then
        With Application
            .DisplayAlerts = False
            wbk.Sheets("Final Report").Delete
            .DisplayAlerts = True
        End With
    End If
End Sub

Open in new window

SumUpLoadFees.xls
0
 

Author Comment

by:JaseS
ID: 35220383
gives an error: run time error 13

highlighted in yellow:
   arrMonthsValues(i, 2) = arrMonthsValues(i, 2) + rngRandom.Value
0
 

Author Comment

by:JaseS
ID: 35220393
when I click the button, it immediately brings Mastercard.xls up front on the screen and then after about 15 secs, produces the error. Would it give an error like that if something was in Col L not an amount?
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220398
yes... most likely... if there's something that is not an amount and/or if there's something on columm K that is not a date...
0
 

Author Comment

by:JaseS
ID: 35220403
I'll do a quick check  - of over 100 pages :{
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220406
no, dont do it...
It's just that, you never mentioned this possibility, so I didnt code considering it.
Give me 15 minutes.

:-)
0
 

Author Comment

by:JaseS
ID: 35220408
and does the amount in Col L have to be directly across, next to, a date in Col K?
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220431
i dont get your question, but...
if there's nothing in column K, then column L's number will not sum up !
it only sums by looking at column K and getting the month from it...

what I could try to do, programatically always populate the empty cells with a date of the month from above it or below it, but i think it's kinda risky though...
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220452
i changed the one line of code in this one, to consider that text can be written in column L...

please let me know how to go about the empty cells on column K...

Should I consider the month from above, or the month from below ??
I would guess a logic here:
if the closest cell above with content has DAY below 15, I consider the same month.
if the closest cell above with content has DAY above 15, I consider the next month...

i honestly dont like this, and i think it's risky because this means the code playing with numbers likely putting them in wrong months.... but it's up to you... in case you decide not to go this path, here is the new file/code with the button. :-)
SumUpLoadFees.xls
0
 

Author Comment

by:JaseS
ID: 35220455
Taking a look at the attached image you can see that the loading fees are not aligned with any date. This is a typical spreadsheet where I need to gather the loading fees for the month and sum them with other sheets of the same layout. sum fees
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220462
i understand... but see, the human factor (you) decides to which month these unrelated data goes...
I need you to explicitly open that logic that goes on in your brain when making this decision, because see, the code will do everything we tell it to do... SO we have to tell it something...

The final question is:
what should the code do with values on column L that are not related to any date on column K ?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:JaseS
ID: 35220477
okay, then what needs to happen is another code (another project) that populates Col K with today's date when an amount is input in Col i. That would put a date in Col K directly next to the value in Col L and that same code needs to calculate the loading fee in Col L and insert it.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220509
ok that's it, we're getting somewhere :-)

So now please provide an example.
In the attached image, I see the first value that K is empty, we have:
97.32 in column L and
4893.08 in column I.
Do you mean that we'll have today's date on column K and the value on column L will change ?
what means "calculate it and insert it" ?

Let's go step by step... in that example that mentioned above, if someone (likely you) enters a value on column i, (i understand then that this new code will be triggered by changing a cell in column I, and not by the button, right ?) what should happen with K and L ?
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220577
dude, I'm tired...
let's do one thing...

I will go home now, and give you more time to think .... and then, tomorrow (or at some point during the weekend) I will come back here and check your reply and adapt the code (or create a new one) with the new logic for column K... sounds good ?

I really do need to go... ttyl !

Fernando
0
 

Author Comment

by:JaseS
ID: 35220615
Here's how it works:

1. I enter an amount in Col F
2. I drag down a formula contained in Col i which kicks off a code
3. that code breaks up the 4893.08 as you see it in Col J, but skips a row when it does
4  it also puts a date next to it in Col K and does so until the entire 4893.08 is used up

What I am needing I see is another function that when I put a value in Col F it does the above and also
populates Col K with today's date and in Col L the Loading fee. Now, I already have the code that does steps 1-4 as detailed above.

So I guess what I need is the code that populates Col K on the same row where the 4,893.08 is with today's date and on the same row calculates the loading fee (which is 1.85% of Col F) and inserts that in Col L.

If you wanted to create this code I will start a related question, you reply and I'll give you the points.

HOWEVER, that does not take care of the current problem of how do we tie the date in Col K to the Loading Fee in Col L. So, what I can do, is go through my sheets and manually put a date in Col K on the same row as the Loading Fee in Col L, that way all loading fees in Col L will have a date right next to it on the same row, in Col K

Does this help?
0
 

Author Comment

by:JaseS
ID: 35220621
also, keep in mind, that I will have a number of workbooks open so I'm not sure having the code work with having an 'active workbook open' will work as I always have more than one open.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220683
I'll have to agree that its a new project therefore new question !
I'll change the active workbook thing then this question is done !
in the new question, it will make it easier if u put the code u have in it, then i can adapt it to do the remaining stuff, then you'll have a complete solution for all your problems :-)
0
 

Author Comment

by:JaseS
ID: 35220699
when you post the code to finish up this question I'll post the related question immediately
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35220714
sounds like a plan ! tomorrow !
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35224745
Jases
sorry it took me a while to get into this today, but I did the best I could to keep my word.
Here's the new file with all the code you need, and changed way of working with multiple workbooks.

1) if there's only one workbook open (it is the one with the button), the code will show the Open Workbook dialog box
2) If there's two workbooks open, it will understand that the one which is not the one with the code, is the one supposed to run the sum
3) If there are multiple workbooks opened (more likely to happen), it will show a dialog box developed in VBA, to help you pick the one you want. It can be selected from the list box then press ok, or double-clicked...

Hope you find it sweet :-)

now I have to go drink some beer ! take care !
SumUpLoadFees.xls
0
 

Author Comment

by:JaseS
ID: 35240394
gosh! I was waiting for an email telling me you posted something and never got one. Sorry about not checking this. I will look this over asap and post the related question we talked about. Thank you!
0
 

Author Comment

by:JaseS
ID: 35240538
Hi Fernando,

Ran the code and it correctly gave me the option of which spreadsheet to use, so that is good.
However, it now is just hanging, the code apparently whirring away for over 5 minutes now and had to do a control-alt-delete to shut it down.

Could you tell me what is required for this code to run successfully? I might have something, or not have something in columns it is checking causing it to hang.
0
 

Author Comment

by:JaseS
ID: 35240639
Hold on! Wow! That took awhile but I got results.  Great!

But still, could you tell me on what sheets the code is looking and what is required for this code to run? I want to make it got to the total correctly.
0
 
LVL 6

Accepted Solution

by:
FernandoFernandes earned 500 total points
ID: 35242083
Jases, I am glad it worked. I just don't get why it's taking so long to run, it shouldn't. But, in all my tests, I had only a couple of sheets which were actually copies of the one you sent.
Maybe if I add control to the calculation, it might improve performance...

please check this one, attached.
SumUpLoadFees.xls
0
 

Author Comment

by:JaseS
ID: 35242801
Now that was fast! About 2 seconds and it came up with a much different number than the other one.
Just so I can make sure that it is getting the right figures, could you tell me on what sheets the code is looking and what is required for this code to run? I want to make it got to the total correctly.

Thank you, though. It seems to work great!
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35242993
well... it checks all the sheets according to the logic requested above.
Only sheets that do NOT start with MC. And it loops through all the sheets within the selected file, except the Final Report one.
Also, it will only add the numbers of sheets that has the Load Fee as text in the row one for any of the columns... if there's no column with this heading, it will not use that sheet.

Please run many times with the same file, in order to see how the results differ (or not)...

Thanks,
0
 

Author Comment

by:JaseS
ID: 35243206
ran three times - all with the same results.

but have a question:
How does it know to grab the different month's Loading fee on each page?
That's what I'm trying to determine to make sure I don't have something on
the sheet or in the columns that is altering the totals.
What has to be in the Date column and the Load Fee column for this to be accurate
and how do they have to relate to one another. You have an example sheet of what the code has to go through. What would make the code work or not work?
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35243337
see, the column K has necessarily to have a date...
the column L has to have numbers... (considering that sometimes it has text, which is not valid)

The code is using a array sum to add up all the values from column L that have the same month in column K... now, the next "project/question" will be how to make sure we populate column K with as much dates as possible, following that rules you mentioned above...

my suggestion is, in order to understand better and in details what the code is doing, you should do two things:

1) read this whole thread again, since the beginning (it will make it more clear all the logic used)
2) (most important for your tests) : Take one of your workbooks, save as different file break it make sure it has only 5 sheets ( or more, or less, just, make it easier to check ;) ) then run the code and verify the values... you will see that it's doing what I mentioned above, and it is ignoring values on column L that has no date right next to it on column K ...
3) repeat number 2 for as many workbooks as you want, then you'll become more confident that the results are accurate...

Makes sense ?

Let me know if you have any more doubts... I'll be happy to clarify.
0
 

Author Comment

by:JaseS
ID: 35243732
I'll go ahead and do as you suggest, Fernando.
Are you up to the second part of this project which is automatically formatting and placing the date, calculating the amount to load and loading fee?

I'm closing this one out and hopefully you'll pick the related question up on the other side.

Thanks for your great work on this one.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35243945
I'll try the other one as well :)

who doesn't like a chalenge ? lol
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35243949
no problem... let's see what the next steps will be !
0
 

Author Closing Comment

by:JaseS
ID: 35244197
Great work! Great guy! Pleasure to work with. He delivered a complex requirement in quick time.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now