Excel VBA: Sum Time per Item in an Array
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
Sum-Elapsed-Time.xls
Thank you, V
Sum-Elapsed-Time.xls
The SUMIF function is faster than an array formula and both will be a lot faster than anything done in VBA.
Kevin
Kevin
ASKER
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
"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
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.
Kevin
Kevin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Cheers
Rob H
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.
Cheers
Rob H
ASKER
An excellent, exhaustive answer! Many thanks!
Cheers, V
Cheers, V
=SUMIF(B2:B11,E2,C2:C11)
Kevin