+ Reply to Thread
Results 1 to 2 of 2

Convert Sumproduct/SumIFS into VBA Dictionary

  1. #1
    Registered User
    Join Date
    02-02-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    8

    Convert Sumproduct/SumIFS into VBA Dictionary

    HI all,

    I have a spreadsheet that has very large table data and currently i am summarsing the data using SUMIFS or SUMPRODUCT and converting into hourly timeseries data and so it takes a while to calculate. The exact data there are a lot of rows

    I had a go at converting them into VBA using both WorksheetFunction and Evaluate SUMIF but it took just as long. I have been researching using Dictionary in VBA which seems very fast however the examples i have seen don't have similar conditions to mine. It just populates a summary using exact conditions, my conditions are greater than or equal to and include dates/times.

    I have attached a simple example of the data table and the format of the results using a simple sumproduct formula. The Summary output criteria are already populated and so it would just be the Output ("Quantity") i'd need in Column M populating.

    Hopefully someone can help.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: Convert Sumproduct/SumIFS into VBA Dictionary

    Have you tried using PowerQuery M? This is now built in to Excel and is faster than the sumifs function.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How can i convert sumifs to sumproduct
    By svergili in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-18-2019, 10:21 AM
  2. Why dictionary very slow ,is dictionary have maksimum data
    By daboho in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-30-2018, 03:57 PM
  3. Convert SUMPRODUCT to SUMIFS
    By teststrip in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-03-2017, 05:15 PM
  4. [SOLVED] Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Function
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-12-2016, 08:28 AM
  5. Dictionary - Using a dictionary of dictionaries to hold individual orders
    By wazimu13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2015, 08:32 PM
  6. [SOLVED] Convert SUMIFS formula to SUMPRODUCT
    By timbury1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-08-2014, 01:18 PM
  7. Using scripting dictionary to do a sumifs
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 10-23-2014, 10:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1