+ Reply to Thread
Results 1 to 10 of 10

TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

  1. #1
    Registered User
    Join Date
    05-21-2020
    Location
    tpe
    MS-Off Ver
    x12
    Posts
    8

    TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    platform: google sheet.

    D1 =3
    E1 = 4
    F1 = 7

    D2=gg12
    E2=13lkk
    F2=1poi2

    To sum numbers in d2:f2 is simple,
    just use this formula by extracting the numbers in the cell:

    =ArrayFormula(SUM(SPLIT(REGEXREPLACE(TRANSPOSE(D2:F2)&"", "[^\d\.]+", "|"), "|")))

    but if I want to SUMIF(D1:F1,"<6", D2:f2) to get the answer 25, also means only calculate D2&E2 because of D1 and E1 are both <6, how to do that?


    VBA or Google script is not allowed.
    Splitting content to other cells and referring to them is not allowed.

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

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    re: GoogleSheets -- couldn't you just use:

    =ArrayFormula(SUM(REGEXEXTRACT(TRANSPOSE(FILTER(D2:F2,D1:F1<6)),"[0-9]+")+0))

  3. #3
    Registered User
    Join Date
    05-21-2020
    Location
    tpe
    MS-Off Ver
    x12
    Posts
    8

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    that is great!! Thanks a lot!!!!!!

    But here comes an advanced one:

    D1~F1 (merged) = 3
    G1~I1 (merged) = 4
    J1~L1 (merged) = 7

    D2=gg12
    E2=13lkk
    F2=1poi2
    G2=56op
    H2=uy78
    I2=oiqwe999
    J2=u78y7
    K2=j82hf
    L2=8273jf

    how to solve this one?

  4. #4
    Registered User
    Join Date
    05-21-2020
    Location
    tpe
    MS-Off Ver
    x12
    Posts
    8

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    many thanks!!

  5. #5
    Registered User
    Join Date
    05-21-2020
    Location
    tpe
    MS-Off Ver
    x12
    Posts
    8

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    Quote Originally Posted by XLent View Post
    re: GoogleSheets -- couldn't you just use:

    =ArrayFormula(SUM(REGEXEXTRACT(TRANSPOSE(FILTER(D2:F2,D1:F1<6)),"[0-9]+")+0))
    if there's only string in the cell an IFERROR could be added to avoid error, still thanks!!
    Last edited by jhs1212; 05-21-2020 at 12:14 PM.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    Please try
    =ArrayFormula(sum(split(TRANSPOSE(REGEXREPLACE(FILTER(D2:L2,split(TEXTJOIN("",,rept(TRANSPOSE(D1:M1&","),FREQUENCY(lookup(column(D1:L1),column(D1:L1)/(D1:L1>0)),lookup(column(D1:L1),column(D1:L1)/(D1:L1>0))))),",")<6), "[^\d]+", "|")),"|")))

  7. #7
    Registered User
    Join Date
    05-21-2020
    Location
    tpe
    MS-Off Ver
    x12
    Posts
    8

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    Quote Originally Posted by Bo_Ry View Post
    Please try
    =ArrayFormula(sum(split(TRANSPOSE(REGEXREPLACE(FILTER(D2:L2,split(TEXTJOIN("",,rept(TRANSPOSE(D1:M1&","),FREQUENCY(lookup(column(D1:L1),column(D1:L1)/(D1:L1>0)),lookup(column(D1:L1),column(D1:L1)/(D1:L1>0))))),",")<6), "[^\d]+", "|")),"|")))
    thanks but how to apply iferror if cells only contain text without numbers?

  8. #8
    Registered User
    Join Date
    05-21-2020
    Location
    tpe
    MS-Off Ver
    x12
    Posts
    8

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    Much more advanced:

    D1~F1 (merged) = 3
    G1~I1 (merged) = 4
    J1~L1 (merged) = 7

    D2=gg12
    E2=13lkk
    F2=1poi2
    G2~I2(Merged) = oiqwe999
    J2=u78y7
    K2=j82hf
    L2=8273jf

    how about this one? XD

  9. #9
    Registered User
    Join Date
    05-21-2020
    Location
    tpe
    MS-Off Ver
    x12
    Posts
    8

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    Quote Originally Posted by jhs1212 View Post
    thanks but how to apply iferror if cells only contain text without numbers?
    and when a cell is empty this formula fails.

  10. #10
    Registered User
    Join Date
    05-21-2020
    Location
    tpe
    MS-Off Ver
    x12
    Posts
    8

    Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

    Answer by myself:

    =ArrayFormula(sum(iferror(split(TRANSPOSE(REGEXREPLACE(FILTER(D2:L2,iferror(split(TEXTJOIN("",,rept(TRANSPOSE(D1:M1&","),FREQUENCY(lookup(column(D2:L2),column(D2:L2)/(D2:L2>0)),lookup(column(D2:L2),column(D2:L2)/(D2:L2>0))))),","),"a")<6), "[^\d]+", "|")),"|"))))

+ 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. Expanding a range of numbers in a single cell
    By nickheffner in forum Excel General
    Replies: 1
    Last Post: 11-22-2016, 10:46 AM
  2. [SOLVED] Countif & Sumif with single and double digit numbers mixed in same column.
    By hoventim in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-15-2015, 03:19 PM
  3. Delete leading zeros from all numbers present in a single, mixed cell
    By daedelous00 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2013, 04:47 PM
  4. Keyword search within a single cell mixed with vlookup
    By ndube50 in forum Excel General
    Replies: 3
    Last Post: 11-16-2010, 12:42 PM
  5. sumif off of a single cell range
    By fireguy7 in forum Excel General
    Replies: 2
    Last Post: 11-25-2009, 03:20 PM
  6. Range of numbers in single cell
    By Silverspell in forum Excel General
    Replies: 7
    Last Post: 11-10-2009, 06:39 AM
  7. [SOLVED] Range of numbers in a single cell
    By DRB in forum Excel General
    Replies: 2
    Last Post: 01-11-2006, 08:20 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