# 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.  Register To Reply

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))  Register To Reply

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?  Register To Reply

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

many thanks!!  Register To Reply

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!!  Register To Reply

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]+", "|")),"|")))  Register To Reply

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?  Register To Reply

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  Register To Reply

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.  Register To Reply

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]+", "|")),"|"))))  Register To Reply