+ Reply to Thread
Results 1 to 5 of 5

partial text match in an array summing the total value

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    partial text match in an array summing the total value

    Hi,

    I have a formula which totals up quite a few columns but only does so when a condition is met (AB7)

    {=SUM((I23:I4000+J23:J4000+S23:S4000+T23:T4000+U23:U4000+V23:V4000+L23:L4000+N23:N4000+P23:P4000+R23:R4000+K23:K4000+M23:M4000+O23:O4000+Q23:Q4000)*(--(F23:F4000=AB7)))}

    It works for exact matches but I want it to be able to do partial text matches for the condition which I can't get to work... I've tried AB7&"*" but this doesn't seem to work. Can you not do partial matches in arrays?

    The text that I want it to find is when it has a prefix "PS". so the following fields that contain PS: PS01, PS02, PS03 etc (these are in column F) would have the range I23:V4000 summed. An example of the non working partial text match is below

    {=SUM((I23:I4000+J23:J4000+S23:S4000+T23:T4000+U23:U4000+V23:V4000+L23:L4000+N23:N4000+P23:P4000+R23:R4000+K23:K4000+M23:M4000+O23:O4000+Q23:Q4000)*(--(F23:F4000=AB7&"*")))}

    Any help would be much appreciated

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,332

    Re: partial text match in an array summing the total value

    See post #3
    Last edited by JohnTopley; 04-21-2018 at 06:08 AM.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,332

    Re: partial text match in an array summing the total value

    Try

    =SUMPRODUCT((I23:V4000)*(ISNUMBER(SEARCH("PS",F23:F4000))))

  4. #4
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: partial text match in an array summing the total value

    Next time I'm stuck I'll do what you suggested... In the meantime that formula worked perfectly!!

    Thank you very much
    Best

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,332

    Re: partial text match in an array summing the total value

    It is always helpful to post a small file (20-30 rows) as what is obvious to the OP (you) may not be those of us trying to find a solution.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. [SOLVED] summing a partial cell with a specified text in the same row
    By preciouslife73 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-24-2017, 03:38 PM
  2. [SOLVED] Index Match with Partial Match in Lookup Array
    By AliGW in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2016, 03:13 PM
  3. INDEX Partial MATCH and Total
    By flapface in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-18-2014, 10:22 AM
  4. Table Array with Wildcard involving partial URL match
    By tworoads in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-29-2014, 09:29 AM
  5. [SOLVED] Match partial text when partial text is not exact
    By NamiSama in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-21-2014, 07:18 PM
  6. [SOLVED] Partial Match Cell To String Array
    By amazinglazers in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-09-2013, 05:56 AM
  7. Partial Match in an Array
    By matrex in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-13-2008, 09:40 PM

Tags for this Thread

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