Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumproduct and Wildcards (Xp and up)

    Hi!

    I am working on a formula for a client of mine and I am truly stuck! I have a workbooked with two worksheets: One which has all the raw data and the other which has formula adding and averaging numbers that are in the raw data.

    The formula I am looking to create has two criteria 1)that will looked for Period 1 in column A, the other being Corp (Corp has to have a wildcard because CORP is just the beginning of the word.) The formula I am using is =SUMPRODUCT(N('Raw Data'!A2:A65000="Period 1"),N('Raw Data'!AL2:AL65000="CORP"),'Raw Data'!B2:B65000). This does not work as Corp has Corpdev, CorpFin, CorpRel etc.

    Is there a way to add a wildcard at the end of CORP so that it looks for all that begins with that value? Or am I approaching this formula all the wrong way?

    Thanks!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct and Wildcards (Xp and up)

    =SUMPRODUCT(N('Raw Data'!A2:A65000="Period 1"),N(UPPER(LEFT('Raw Data'!AL2:AL65000,4))="CORP"),'Raw Data'!B2:B65000)

    Be aware, that using SUMPRODUCT over such a large area will cause recalc to become quite slow. I'm not even sure if Excel can cope with such large arrays in a matrix function like SUMPRODUCT.

    You might be much better off using the database functions, like DSUM to solve this problem.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct and Wildcards (Xp and up)

    Another option,

    =SUMPRODUCT(--('RawData'!A2;A65000="Period 1"),--ISNUMBER(SEARCH("CORP",'RawData!AL2:AL65000)),'Raw Data!B2:B65000)

    As has been pointed out 64999 rows is a lot for Sumproduct and having too many Sumproduct's will slow your calculation down. Try to use relevant ranges.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct and Wildcards (Xp and up)

    Thank you both!

    Thanks for the advice on the range. Unfortunately, the range changes all the time. And currently I have about 25 formulas referencing the range. It would be more time consuming to change it every time.

Posting Permissions

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