Parse Sum

[Stephen_IV]

Fluff so graciously helped me out yesterday, I have been trying to find a formula that will sum all values with the name Mark in column A and then sum the position in column B. I have a helper column but would like a formula to do it without it. Any help would be appreciated.

Book1 A B C D E F G 1 Names = Mark Sum Vals Mark 1350 2 Mark 123 123 3 Paul/Don/Jeff/Mark 234/987/728/910 910 4 Don/Mark 119/211 211 5 Fred 23 0 6 Mark 34 34 7 Al/Frank/Fred 345/928/1199 0 8 Paul 345 0 9 Mark/Paul 35/9177 35 10 Al/Kenny/Fred 45/87/43 0 11 George/Larry/Don 46/526/12 0 12 Terry/Jim/Tom /Don/Fred/Mark/Jeff/Nick 120/32/43/54/23/37/8 37 Sheet1 Cell Formulas Range Formula G1 G1 =SUM(C2:C12) C2:C12 C2 =IF(IFNA(MATCH("Mark",TEXTSPLIT(A2,"/"),0),0)>0,INDEX(TEXTSPLIT(B2,"/"),IFNA(MATCH("Mark",TEXTSPLIT(A2,"/"),0),0)),0)*1

 

[Fluff]

How about

=SUM(BYROW(A2:B12,LAMBDA(br,--IFNA(INDEX(TEXTSPLIT(INDEX(br,,2),"/"),MATCH(F1,TEXTSPLIT(INDEX(br,,1),"/"),0)),0))))

 

[Georgiboy]

Still trying to get my head around the new 365 functions, below was my attempt - not sure if it is the best way to go about it:

=SUM(VALUE(IFERROR(MAP(A2:A12,B2:B12,LAMBDA(a,b,INDEX(TEXTSPLIT(b,"/"),MATCH(F1,TEXTSPLIT(a,"/"),0)))),0)))

Fluff:
In your formula above does: INDEX(br,,2) refer to column 2 of A2:B12?

 

[Stephen_IV]

Thank you both!!!! Did exactly what I needed!!! Fluff thanks for the second time this week!!!!!

 

[Fluff]

In your formula above does: INDEX(br,,2) refer to column 2 of A2:B12?

That's right.

below was my attempt - not sure if it is the best way to go about it:

If it works that's what counts, what's more it's better than mine in that it will work if there is a blank cell in the range.

 

[Fluff]

Thank you both!!!!

Glad we could help & thanks for the feedback.

 

[Georgiboy]

That's right.

If it works that's what counts, what's more it's better than mine in that it will work if there is a blank cell in the range.

Thanks Fluff - this whole LAMBDA thing is blowing my mind

Thank you both!!!! Did exactly what I needed!!! Fluff thanks for the second time this week!!!!!

Happy to help - while helping myself

 

[shinigamilight]

So I was testing some stuff and turns out textsplit function of excel 365 doesn't work on a range or array. So I made one myself which does.
I cannot find any bugs with it, let me know if you find any.
If anyone wanna try it out i attached the code with the formula below

=SUMPRODUCT(IFERROR(((xlTEXTSPLIT(A2:A12,"/")="Mark")*(xlTEXTSPLIT(B2:B12,"/"))),0))

Function XLTEXTSPLIT(D As Variant, DELIM As String) As Variant
            Dim v, FinAr, s, Temp, EA
            Dim m, MyR, MyC As Long
            v = D
            ReDim FinAr(1 To UBound(v), 1 To 1)
            
            For Each s In v
                    
                    Temp = Split(s, DELIM)
                    m = UBound(Temp) + 1
                    If m > UBound(FinAr, 2) Then ReDim Preserve FinAr(1 To UBound(v), 1 To m)
                    MyR = MyR + 1
                    MyC = 0
                    
                            Do
                    For Each EA In Temp
                            MyC = MyC + 1
                            FinAr(MyR, MyC) = EA
                    Next EA
                          Loop Until MyC = m
                    
                    
            Next s
                
            XLTEXTSPLIT = FinAr
        
        
End Function