+ Reply to Thread
Results 1 to 4 of 4

SUM an array that contains some text values and partial text strings

  1. #1
    Registered User
    Join Date
    05-04-2020
    Location
    New York
    MS-Off Ver
    2016
    Posts
    12

    SUM an array that contains some text values and partial text strings

    Hi, I'm a new excel user and could use some help. I'm trying to get a SUM for an array that looks like this:

    DxPQicx.png

    I've tried several different variations of SUMIF such as:

    {=SUMIF(Q9:Q19,"<>"&"-")} returns 0
    {=SUMIF(Q9:Q19,"+*"} returns 0

    Using (Q9:Q19)*1 instead of (Q9:Q19) gives me a syntax error. Any help would be greatly appreciated. Thank you.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: SUM an array that contains some text values and partial text strings

    per your screenshot + literal strings

    =SUMPRODUCT((Q9:Q19&".0")+0)

  3. #3
    Registered User
    Join Date
    05-04-2020
    Location
    New York
    MS-Off Ver
    2016
    Posts
    12

    Re: SUM an array that contains some text values and partial text strings

    Thank you very much!

    I also found a much less elegant solution: =SUM(NUMBERVALUE(RIGHT(Q9:Q19,LEN(Q9:Q19)-1)))

    Does the *".0" convert the text to a number and the +0 is basically just a dummy to force the function to calculate?

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: SUM an array that contains some text values and partial text strings

    Does the *".0" convert the text to a number and the +0 is basically just a dummy to force the function to calculate?
    the .0 is appended to every string so "+12" becomes "+12.0" and, much more importantly, "-" becomes "-.0"
    the +0 coerces the resulting strings to a number ... you cannot coerce "-" but you can coerce "-.0"
    the outer SUMPRODUCT then aggregates the coerced values.

    your approach is more robust, i.e. would handle decimals etc
    [edit: but less so should you ever have negative values!]
    Last edited by XLent; 05-04-2020 at 09:18 AM.

+ 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] Please help on COUNTIF Partial text, partial text/words also contained in other texts
    By maria.h in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2020, 10:18 PM
  2. [SOLVED] partial text match in an array summing the total value
    By mrsak87 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-21-2018, 06:19 AM
  3. [SOLVED] Search partial text in an array
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-11-2016, 03:47 AM
  4. Replies: 2
    Last Post: 03-01-2016, 08:01 PM
  5. [SOLVED] Find Partial text in an array and extract charaters to right
    By jenita.kurlawala in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-02-2013, 08:03 AM
  6. Replies: 4
    Last Post: 07-27-2013, 07:38 AM
  7. Help searching an array of text strings for common strings
    By ABComp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-23-2012, 11:19 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