+ Reply to Thread
Results 1 to 10 of 10

Help to Convert to User Defined Function-Sumproduct

  1. #1
    Registered User
    Join Date
    03-06-2008
    Posts
    20

    Help to Convert to User Defined Function-Sumproduct

    I have a workbook that contains two worksheets: sheet 1 is a control list and sheet 2 uses sumproduct to get info from constrol list. The following formula is in sheet 2.

    Is it possible to convert the following Excel formula to user defined function?


    IF(SUMPRODUCT(('ControlList'!$N:$N=G$3)*('ControlList'!$R:$R=$A5),'ControlList'!$A:$A)>0,SUMPRODUCT(('ControlList'!$N:$N=G$3)*('ControlList'!$R:$R=$A5),'ControlList'!$A:$A),"")

    Thank you in advance.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Help to Convert to User Defined Function-Sumproduct

    Have you tried:
    Please Login or Register  to view this content.
    ?

    Note - I think there is a limit to the number of characters evaluate can cope with so you might need to split it up. I didn't know which bits were variable/fixed so just changed the stuff without $ signs to input variables.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,876

    Re: Help to Convert to User Defined Function-Sumproduct

    Yes, I guess, but why would you want to do that?

    If it's because it is slow, it will be because you have full column references. And it will ned to be evaluated twice, once to see if the value is zero, and again for the actual result. So, you're multiplying a million+ values by a million+ values by a million+ values ... twice.

    You'd be better creating Dynamic Named Ranges, or, even better, converting your data to a Table and using structured Table references.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,876

    Re: Help to Convert to User Defined Function-Sumproduct

    Interestingly, ragulduy's profile says Excel 2003. You can't have full column references in Excel 2003 and it is bad practice in more recent versions.


    Regards, TMS
    Last edited by TMS; 04-03-2014 at 09:59 AM.

  5. #5
    Registered User
    Join Date
    03-06-2008
    Posts
    20

    Re: Help to Convert to User Defined Function-Sumproduct

    Thanks but it seems the UFD does not work out.

    I would like the cells ("G$3"), cells ("$A5") to be dynamic and the formula should also work when the cells are cells ("H$3"), Cells ("$A6").

  6. #6
    Registered User
    Join Date
    03-06-2008
    Posts
    20

    Re: Help to Convert to User Defined Function-Sumproduct

    The Excel function runs too slow so I need a UDF to solve the problem. Please help.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,876

    Re: Help to Convert to User Defined Function-Sumproduct

    Did you read my reply. It's slow because of the full column references. No other reason

    Regards, TMS

  8. #8
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Help to Convert to User Defined Function-Sumproduct

    @OP

    I agree with TMS, the userdefined function will just be the same computationally (if not worse). I had assumed you just didn't want to type out the whole formula every time.

  9. #9
    Registered User
    Join Date
    03-06-2008
    Posts
    20

    Re: Help to Convert to User Defined Function-Sumproduct

    Yes you are right. I have changed the formula by narrowing the range. The formula runs faster now.

    But may I still want to learn how to use a dynamic range in the UDF because the control list is getting longer.

    Thank you.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,876

    Re: Help to Convert to User Defined Function-Sumproduct

    I suggest you post a sample workbook with some typical data. Then we can help you to create a Dynamic Named Range, or two, or three. Or, better, a Table.

    A UDF is not the answer. Especially, if it simply evaluates a worksheet formula. Given that you are looking at 3 columns, you'd have to try to make it flexible enough to cope with whatever ranges you needed it to use. And it would only be relevant to a specific task.

    Regards, TMS

+ 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] VBA / User Defined Function for an Antiparallel SUMPRODUCT
    By GrahamJD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2013, 10:15 AM
  2. Convert Array Formula into VBA User Defined Function
    By hhhhwong in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-05-2013, 12:38 AM
  3. [SOLVED] User defined function returns an error on a standard function used in it.
    By pb48 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-23-2013, 01:35 PM
  4. User Defined Function - Sumproduct
    By PeterW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-23-2008, 09:20 PM
  5. [SOLVED] Excel - User Defined Function Error: This function takes no argume
    By BruceInCalgary in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2006, 04:05 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