+ Reply to Thread
Results 1 to 8 of 8

custom function instead of sumproduct

  1. #1
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    485

    custom function instead of sumproduct

    Hi,
    I am using sumproduct formula
    =sumproduct(--(d3=sheet2!d:d),(e:e))
    This slows my sheet..
    i know that this is volatile formula and used sum if.. but i cannot open the another workbook and keep.. sheet2 reference is from another workbook..
    SO i want a macro to lookup and sum all the instances..

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,783

    Re: custom function instead of sumproduct

    It's slow because you are using full column references. Restrict it to a more specific range ... maybe a 1000 rows?

  3. #3
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    485

    Re: custom function instead of sumproduct

    Yes, but i dont know when the source workbook will be updated.. so only i gave full reference.. as i dont know the last row( as the source file will be updated every now and then)

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,249

    Re: custom function instead of sumproduct

    Quote Originally Posted by dorabajji View Post
    Yes, but i dont know when the source workbook will be updated.. so only i gave full reference.. as i dont know the last row( as the source file will be updated every now and then)
    Do you have an idea of what the maximum number of rows will ever be (1000, 5000, 10000, or whatever)? All you have to do is choose a range that should never be exceeded by the data, ever, but that is significantly smaller than the full column reference.

  5. #5
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    485
    Quote Originally Posted by Rick Rothstein View Post
    Do you have an idea of what the maximum number of rows will ever be (1000, 5000, 10000, or whatever)? All you have to do is choose a range that should never be exceeded by the data, ever, but that is significantly smaller than the full column reference.
    Hi Rick,
    At present it is 700.. each month average entries is around 250 to 400.. that workbook is updated by sales staff. I have to report the summary from this workbook to manager. So, I don't have this source file .. only the formula file, I will be having.. source file will be in d network folder.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,783

    Re: custom function instead of sumproduct

    400 x 12 = 4800. So, if you allowed 10,000 that should be more than twice what you need. To be on the (very) safe side, you could allow 20,000 or even 50,000 ... still way better than over a million rows.

    It will still be safe and way better performance.

  7. #7
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,249

    Re: custom function instead of sumproduct

    TMS has posted the answer where I was going with my question. My calculation was going to be 400 * 12 (months) * 10 * (years) which would be 48,000 so I was going to recommend 50,000 (TMS's upper limit)... that would cover you for about 10 years which I'm guessing should be more than enough. If you don't think you need to cover that much into the future, you might consider using a range of 25,000 which would cover 5 years worth of updates instead.
    Last edited by Rick Rothstein; 03-28-2020 at 05:46 PM.

  8. #8
    Forum Contributor
    Join Date
    01-28-2008
    MS-Off Ver
    Excel 2016
    Posts
    151

    Re: custom function instead of sumproduct

    Can you put the sales data in a “formal table“ so that the column being referenced will automatically update as new data are appended?

+ 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. [SOLVED] Usage of standard functions into custom function (or user defined function)
    By tusharb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2016, 12:43 AM
  2. Replies: 0
    Last Post: 11-20-2015, 12:34 PM
  3. VBA code for custom function that returns detail results of array function
    By onechipshot in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2015, 06:30 PM
  4. Replies: 9
    Last Post: 07-02-2012, 07:02 PM
  5. Insert function - custom function name preceded by module name
    By dtshedd@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2006, 10:50 AM
  6. [SOLVED] Custom Function: Detecting the cell the function is used in
    By g-boy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2005, 02:05 PM
  7. [SOLVED] Emulate Index/Match combo function w/ VBA custom function
    By Spencer Hutton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2005, 01:06 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06642 seconds
  • Memory Usage 9,080KB
  • Queries Executed 15 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (8)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (8)postbit_legacy
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (48):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1