• Hello

    I'm trying to write a UDF that will get round a problem with SUMPRODUCT. Basically, I have a large DataTable with about 180 columns of data in it. I need to be able to work out the number of entries in the table that match certain criteria (stored in a range called Criteria). In principle, the number of criteria could equal the number of columns, and I'm working on that basis in what follows.

    In other words, I could use COUNTIFS, but I can't guarantee that all users will have Excel 2007 or newer, and I've read that the max number of criteria for COUNTIFS is 157 (<180).

    So I'm therefore having to go for a SUMPRODUCT formula along the lines of


    Where in this test example, the DataTable range is D4:F11 and the Criteria range is G4:I4.

    The problem is that I can't use wildcards in the above. So if I want to set I4 = "*" to mean all of column F4:F11, then I can do this using COUNTIFS, but not SUMPRODUCT.

    I'm therefore trying to write a UDF to produce the SUMPRODUCT formula automatically, so that if an entry in the criteria column is "*" or "", then it skips that column in the DataSheet/Criteria ranges and moves on to the next. I've come up with the code below and it comes up with a big juicy #VALUE! error when I try.

    The UDF has been declared in the VBA module (not the sheet), so that's not the problem. Any suggestions for what I'm doing wrong?

    Many thanks in advance,

  • Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT


    made couple of changes


  • Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

    I would agree with Stephen that Advanced Filter would be the way to go. It has all the capabilities of COUNTIF(S) criteria and more since you can create AND/OR functions. You might want to use some VBA to build the criteria but the performance of the filter will be much faster than a UDF.

  • Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

    You can do this with a SUMPRODUCT formula. Might not be the best approach for you but this version of your formula will treay blanks or * in the criteria range as an "any value" criteria


    Note that SUMPRODUCT lets you have up to 255 arrays in Excel 2007 (but it's limited to 30 in Excel 2003)

    COUNTIFS, btw has a limit of 127 range/criteria pairs

  • Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT


    HUGE thanks for all your help and suggestions.

    I tried it both with an Advanced Filter and with the modified UDF suggested by daddylonglegs (and code as modified by Krishna). I've got both up and running and will do some speed testing to see which works best when used for real.

    In case anybody else wants to use it, here's the final version of the code for the UDF:

    Regards - and thanks, once again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!