Excel VBA: Sum Time per Item in an Array

Posted on 2011-05-01
Medium Priority
Last Modified: 2012-05-11
Hello Experts - I need a VBA code that will look up multiple occurence of items in an array and report (say, in a msgbox)  the sum of time per unique category of items. Total time per item is to be in the same format as individual values in the list, ie. "[h]:mm:ss;@". Conditional sum is a slow and won't do it with a large array.  I attached a file with sample data.

Thank you, V
Question by:NewToVBA
  • 4
  • 2
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35500941
Use the SUMIF function. It's as fast as you will get:


LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35500944
The SUMIF function is faster than an array formula and both will be a lot faster than anything done in VBA.


Author Comment

ID: 35501235
Have you tried  cond Sum on a 65 k lines array on in Excel 2003 or earlier? Your CPU will get cooked,
"caclulating" will stay forever, and if other macro is wating for the output things will get ugly.

I actually included in the test file a sum formula to domonstrate that had gone that avenue...

Cheers, V
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35501243
You used a SUM function in an array formula. Those are much slower than a SUMIF. Try the SUMIF and see how much that improves performance. VBA will be a lot slower than either of those solutions.

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 1000 total points
ID: 35502123
I did a test. I used three test cases. Your SUM array formula, my SUMIF formula, and the fastest VBA solution I could build. I did 10,000 calculations over 65,000 rows of data.

Your SUM array formula: 2 minutes and 42 seconds
My SUMIF formula: 21 seconds.
My VBA UDF: 5 minutes and 48 seconds.

Here is the VBA code if you want it:

Public Function MySum(ByVal Name As String) As Date

    Dim Data As Variant
    Dim Row As Long
    Data = Sheet1.[B2:C65000]
    For Row = 1 To 64999
        If Data(Row, 1) = Name Then MySum = MySum + Data(Row, 2)
    Next Row

End Function

LVL 34

Expert Comment

by:Rob Henson
ID: 35506908
In addition to Kevin's suggestion, 2007 and later have the SUMIFS function whereby you can put more than one condition.

If 2003, you could also use the DSUM function but you would have to setup a criteria table for each formula with a different set of criteria.

Rob H

Author Closing Comment

ID: 35513902
An excellent, exhaustive answer! Many thanks!
Cheers, V

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 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