+ Reply to Thread
Results 1 to 2 of 2

COUNTIF/COUNTIFS function + nested user-defined function

  1. #1
    Registered User
    Join Date
    04-07-2015
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    2

    COUNTIF/COUNTIFS function + nested user-defined function

    Hi,

    I am a new member who recently joined because I find the site useful for my line of work. I use excel everyday and I am getting fond of using it now for almost every type of automated function in my computer.

    Most of the time, I do not have to look far to solve a problem and I usually solve the complex function in excel on my own after getting a rough idea of the formula/vba code I need from this forum and then modify it on my own successfully.

    But I have encountered an unsolvable problem today and I guess its about time I introduce myself .

    Problem:

    I am trying to get excel to count colors in an array of cells. I got the VBA code to solve counting colors using a user-defined code form one of the members here, and I call it countcolorif(referencecolorcell,rangeofcolorcell). Because it is stored in PERSONAL.XLSB, I have to reference it when I call it in a cell by adding "personal.xlsb!" prefix to the function.

    That part was easy, until I decided to combine with the COUNTIFS function;

    =COUNTIFS(K17:U17,PERSONAL.XLSB!countcolorif(K13,K17:U17),K16:U16,1)

    Basically, I am trying to count colored cells that correlates under its category; in this case I want to count the number of colored cells of the reference color in that range K17 to U17. which correlates with category "1" of multiple categories in range K16 to U16.

    Under normal circumstances wihout using a userdefined function, countifs will get me the correct result. But with this one its giving me back 0, which means it is not evaluating my userdefined function.

    So im looking for a solution here if there is one. Thanks alot.

  2. #2
    Registered User
    Join Date
    04-07-2015
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    2
    Quote Originally Posted by shamjamali View Post
    Hi,

    I am a new member who recently joined because I find the site useful for my line of work. I use excel everyday and I am getting fond of using it now for almost every type of automated function in my computer.

    Most of the time, I do not have to look far to solve a problem and I usually solve the complex function in excel on my own after getting a rough idea of the formula/vba code I need from this forum and then modify it on my own successfully.

    But I have encountered an unsolvable problem today and I guess its about time I introduce myself .

    Problem:

    I am trying to get excel to count colors in an array of cells. I got the VBA code to solve counting colors using a user-defined code form one of the members here, and I call it countcolorif(referencecolorcell,rangeofcolorcell). Because it is stored in PERSONAL.XLSB, I have to reference it when I call it in a cell by adding "personal.xlsb!" prefix to the function.

    That part was easy, until I decided to combine with the COUNTIFS function;

    =COUNTIFS(K17:U17,PERSONAL.XLSB!countcolorif(K13,K17:U17),K16:U16,1)

    Basically, I am trying to count colored cells that correlates under its category; in this case I want to count the number of colored cells of the reference color in that range K17 to U17. which correlates with category "1" of multiple categories in range K16 to U16.

    Under normal circumstances wihout using a userdefined function, countifs will get me the correct result. But with this one its giving me back 0, which means it is not evaluating my userdefined function.

    So im looking for a solution here if there is one. Thanks alot.
    So I have attached a screenshot of the worksheet for some visual reference, sorry if its not that clear as i am uploading it from my phone.
    Attached Images Attached Images

+ 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. Replies: 1
    Last Post: 07-19-2014, 06:29 AM
  2. Creating a deeply nested IF MATCH user defined function
    By rrbest in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2011, 01:23 PM
  3. User Defined Function: Nested Ifs?
    By indianagreg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2010, 11:15 PM
  4. [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
  5. Replies: 0
    Last Post: 06-20-2006, 10:55 AM

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