+ Reply to Thread
Results 1 to 6 of 6

SUMPRODUCT with multiple criterias, multiple search on same column and with wild card

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    SUMPRODUCT with multiple criterias, multiple search on same column and with wild card

    Hello,

    Say I have the following table

    A B C D E F G H I J
    1 ID Account Jan 2016 Search Criteria 1
    2 1000 PL490 10 1000 1001 1002 1003 1004 1005
    3 1001 PL491 20 Search Criteria 2
    4 1002 PL492 15 PL49*
    5 1003 PL492 25
    6 1004 PL491 30
    7 1005 PL490 35

    I had the following 2 SUMPRODUCT formula, but neither gave the correct result.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    So may I ask how can I use the SUMPRODUCT that enables to take multiple search on the same column and on the other criteria search using a wild card?

    Please note that using SUM and SUMIF array could work, but higher ups are finding the formula to be taking a long time to calculate and thus had been told to avoid using it completely!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUMPRODUCT with multiple criterias, multiple search on same column and with wild card

    This worked on my machine.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUMPRODUCT with multiple criterias, multiple search on same column and with wild card

    About the second formula:

    That syntax of SUMPRODUCT (comma separated) does not do well when the arrays are two dimensional or orthogonal, but they do work when multiplied.

    If you wish to use the comma separated syntax then this part --($A$2:$A$7=$E$2:$J$2) must be TRANSPOSED. Of course the formula now has to be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    That formula looks like this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 01-24-2017 at 06:56 PM.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMPRODUCT with multiple criterias, multiple search on same column and with wild card

    SUMPRODUCT won't accept wildcards.

    Is this what you're wanting to do?

    =SUMPRODUCT(--ISNUMBER(MATCH(A2:A7,E2:J2,0)),--(LEFT(B2:B7,4)="PL49"),C2:C7)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: SUMPRODUCT with multiple criterias, multiple search on same column and with wild card

    This worked!

    Solved and reps up to you!

    Quote Originally Posted by FlameRetired View Post
    This worked on my machine.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUMPRODUCT with multiple criterias, multiple search on same column and with wild card

    Glad to hear it! And thank you!

+ 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. Sumproduct of multiple column (same row) that meet multiple criterias
    By dirdjo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-28-2016, 03:57 AM
  2. [SOLVED] Need a wild card in a formula for counting incidents with multiple criteria
    By jmcole in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2014, 10:39 PM
  3. [SOLVED] Vlookup with wild-card and multiple lookup values
    By Jason15 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-28-2014, 09:42 PM
  4. Max, Min, & Std Dev with Multiple Criteria Including a Wild Card
    By rmmohan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2013, 12:45 PM
  5. Move multiple files with wild card name
    By randell.graybill in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2010, 10:31 PM
  6. sumproduct with a search and wild card
    By Scorpvin in forum Excel General
    Replies: 1
    Last Post: 06-14-2006, 11:50 AM
  7. Wild Card Search
    By roy.okinawa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-30-2006, 06:45 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