Tough to find the right formulas to verify these info!

[hnt007]

Hi everyone!

First of all, thank you so much in advance for your help! I've been stuck for days on this....

I have a cell with:
Composer A (BMI) 50% [292650159], Composer B (ASCAP) 30% [154541151], Composer C (SESAC) 5% [55547898], Composer D (SESAC) 5% [4332233], Composer E (SOCAN) 10% [0099887]

(BMI, ASCAP, SESAC, and SOCAN are the royalty distribution companies these composers belong to, the percentages represent their shares of the song, the numbers between [ ] are their ID numbers with the royalty companies)

1)I'm trying to find a formula to put in Conditional Formatting that will highlight this cell in green if the sum of the percentages equals 100 to verify that the splits are correct

2)I have another cell with:
Publisher A (BMI) 50% [12345612], Publisher B (ASCAP) 40% [415156115], Publisher C (SESAC) 10% [4441121254]

I'm trying to find a formula to highlight this cell in green if the percentages for BMI and SESAC match the ones in the cell with the composers AND if the sum of the percentages in this cell with publishers equals 100.

(You can see that 2 composers are with SESAC, so 5%+5%, the publisher with SESAC should have 10%. All composers who are not with BMI or SESAC would be considered to be with ASCAP, that's why you don't see SOCAN in the publishers, and that's why ASCAP has 30%+10% here.)

Thanks!!!

 

[rlv01]

Here's one way to do part 1

Book1
	A	B
1	Data Col	Helper Col
2	Composer A (BMI) 50% [292650159], Composer B (ASCAP) 30% [154541151], Composer C (SESAC) 5% [55547898], Composer D (SESAC) 5% [4332233], Composer E (SOCAN) 10% [0099887]	100
3
4
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SumPct(A2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	Expression	=$B2=100	text	NO

'User defined function to sum the percentages
Function SumPct(DataStr As String) As Double
    Dim SA As Variant, S As String
    Dim I As Long, PctSum As Double

    SA = Split(DataStr, " ")
    For I = LBound(SA) To UBound(SA)
        S = SA(I)

        If InStr(S, "%") > 0 Then
            S = Replace(S, "%", "")
            If IsNumeric(S) Then
                PctSum = PctSum + Val(S)
            End If
        End If
    Next I
    SumPct = PctSum
End Function

 

[rlv01]

Part 2

Book1
	A
1	Composer Cell
2	Composer A (BMI) 50% [292650159], Composer B (ASCAP) 30% [154541151], Composer C (SESAC) 5% [55547898], Composer D (SESAC) 5% [4332233], Composer E (SOCAN) 10% [0099887]
3
4	Publisher Cell
5	Publisher A (BMI) 50% [12345612], Publisher B (ASCAP) 40% [415156115], Publisher C (SESAC) 10% [4441121254]
6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5	Expression	=AND(SumPct($A$5)=100,BMI_SESAC($A$5)=BMI_SESAC($A$2))	text	NO
A2	Expression	=SumPct($A$2)=100	text	NO

'User defined function to sum the BMI_SESAC percentages
Function BMI_SESAC(DataStr As String) As Double
    Dim SA As Variant, S As String
    Dim I As Long, PctSum As Double, Skip As Boolean

    SA = Split(DataStr, " ")
    For I = LBound(SA) To UBound(SA)
        S = SA(I)

        If Left(S, 1) = "(" Then
            Select Case S
            Case "(BMI)", "(SESAC)"
                Skip = False
            Case Else
                Skip = True
            End Select
        End If

        If Not Skip And InStr(S, "%") > 0 Then
            S = Replace(S, "%", "")
            If IsNumeric(S) Then
                PctSum = PctSum + Val(S)
            End If
        End If
    Next I
    BMI_SESAC = PctSum
End Function

 

[hnt007]

Part 2

Book1
	A
1	Composer Cell
2	Composer A (BMI) 50% [292650159], Composer B (ASCAP) 30% [154541151], Composer C (SESAC) 5% [55547898], Composer D (SESAC) 5% [4332233], Composer E (SOCAN) 10% [0099887]
3
4	Publisher Cell
5	Publisher A (BMI) 50% [12345612], Publisher B (ASCAP) 40% [415156115], Publisher C (SESAC) 10% [4441121254]
6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5	Expression	=AND(SumPct($A$5)=100,BMI_SESAC($A$5)=BMI_SESAC($A$2))	text	NO
A2	Expression	=SumPct($A$2)=100	text	NO

'User defined function to sum the BMI_SESAC percentages
Function BMI_SESAC(DataStr As String) As Double
    Dim SA As Variant, S As String
    Dim I As Long, PctSum As Double, Skip As Boolean

    SA = Split(DataStr, " ")
    For I = LBound(SA) To UBound(SA)
        S = SA(I)

        If Left(S, 1) = "(" Then
            Select Case S
            Case "(BMI)", "(SESAC)"
                Skip = False
            Case Else
                Skip = True
            End Select
        End If

        If Not Skip And InStr(S, "%") > 0 Then
            S = Replace(S, "%", "")
            If IsNumeric(S) Then
                PctSum = PctSum + Val(S)
            End If
        End If
    Next I
    BMI_SESAC = PctSum
End Function

Thank you sooo much for your help! Unfortunately, the SumPct doesn't work in my Excel, I'm not sure why!?

 

[rlv01]

I'm not sure either, since it works ok for me. Did you put it in a VBA code module? Function SumPct is a custom VBA function. Unless you are at least somewhat familiar with VBA, I'm not sure you will be able to benefit from this. To be available as a function in your worksheet, Function SumPct must be added to a VBA (macro) code module that you add to your workbook.

 

[hnt007]

I added the VBA codes and it's now working, thank you so much for taking the time to help me out!!