# Thread: Sumproduct and Wildcards (Xp and up)

1. ## 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. ## 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.

3. ## 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. ## 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
•