Sumifs Alternative in VBA dictionary

reggieneo

New Member
Joined
Jun 27, 2017
Messages
26
Hi All,
I have a 2250 cells with sumifs formula. Can somebody please help me to create a sumifs vba alterative that I have built for 2250 Cells.

Need to get the sum of work hours for each work ticket for each Employee.

my data is about 20 thousand rows and just 3 columns.. DATA is growing.

I want to place the answer to worksheet 2 from N to AB Column.

worksheet 2 Row N3 to AB3 is the Criteria for Employee
worksheet 2 Row B4 to B150 is the Criteria for work ticket


Worksheet 1 A3:D20000 is the source data

Worksheet 1 A3:A20000 is the Employee Rows
Worksheet 1 B3:B20000 is the work ticket Rows
Worksheet 1 C3:C20000 is the work hours I need to sum

please help.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Why do you want a VBA alternative?
Any function you create to do it in VBA will most likely be less efficient and more problematic that Excel's built-in functions.

Note that you CAN use most Excel functions in VBA using Application.WorksheetFunction, i.e. Application.WorksheetFunction.SumIfs(...)

Also note that this type of task may be more down more efficiently with Pivot Tables, Power Pivot, or a relational database program like Microsoft Access.
 
Upvote 0
My data presentation is a combinations of p pivot, vba and normal excle function. It’s a mini app i have created and i see vba dictionary fit. Unfortunately my dictionary knowledge is still little . Thanks
 
Upvote 0
A general rule of thumb is that it is seldom more efficient to recreate native Excel functionality.
Volatility and the dynamic-nature of Excel functions can be hard to replicate in VBA, and even when you do, performance often takes a big hit (I have seen workbooks slow down to a crawl).
 
Upvote 0
A general rule of thumb is that it is seldom more efficient to recreate native Excel functionality.
Volatility and the dynamic-nature of Excel functions can be hard to replicate in VBA, and even when you do, performance often takes a big hit (I have seen workbooks slow down to a crawl).
Totally agree.
I have not fully mentioned that 2200 cells with sumifs formula will be replicated month on month or every sheet month. And it needs to be done in single click, which is currently being the case now. That’s is the only way I know it work. Thanks for ur reply.
 
Upvote 0
I have not fully mentioned that 2200 cells with sumifs formula will be replicated month on month or every sheet month. And it needs to be done in single click, which is currently being the case now.
That definitely screams relational database (SQL or Microsoft Access)!
While it relational database work can be done in Excel, that is really not what it was designed for, so it is cumbersome and slow (especially as the data size grows).

If you are stuck using Excel, note that you can use VBA to populate all the SUMIFS formulas on the sheets, with a single click.
 
Upvote 0
I don’t wish to populate those cells with sumifs, I am doing that already now and it’s so slow. I need it in vba sort of dictionary sum. That’s what I am asking for help .
 
Upvote 0
What I am telling you is that just about anything you try to come up with in VBA will probably be slower!
VBA does not typically replace native Excel functions/processes and make them faster!
VBA is not some magic "fix-all" solution that makes all problems go away.

Changing the formulas to hard-coded values will make it faster after you change it, though you lose all dynamic nature of the data (i.e. if you changed some data, other data would not update).
If you really insist on using Excel, and not some relational database program (which is designed for this sort of thing), I think you would probably be better off trying to use Power Pivot instead of VBA here. Power Pivot is a feature of Excel that kind of brings relational database concepts and functions into Excel.

Power Pivot is part of Power BI. There is a forum on this board for that: Power BI
If you look at the first 3 stickies in that forum, it gives you a bunch of information on it. And you can post Power Pivot questions to that forum.
 
Upvote 0
Hi Akuni,
Please see attached file.

This is the sumifs that used, final is the result should be in the worksheet2, but for the sake of illustration I put them all in one image (sheet).
formula: "=SUMIFS($C$2:$C$28,$A$2:$A$28,$G$1,$B$2:$B$28,E2)"


thanks in advance.

1635917527104.png

thanks
 

Attachments

  • sumifs work hours 2.PNG
    sumifs work hours 2.PNG
    48 KB · Views: 5
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top