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

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

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. ## 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. ## 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. ## Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

many thanks!!

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

Originally Posted by XLent
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!!

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

=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. ## Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

Originally Posted by Bo_Ry
=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. ## Re: TOP CHALLENGE!! Sumif every single cell in sum range is mixed with numbers and strings

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

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

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

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

=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]+", "|")),"|"))))

There are currently 1 users browsing this thread. (0 members and 1 guests)

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