How to format row if sum value of row equal specific value

[trungtb]

I use excel and i have sheet look like :

A B C
1 3927269 58516305
2 2356361
3 2749088
4 294545
5 13156350
6 1570908
7 1963634
8 5105449
9 3632724
10 3141815
11 7996358
12 3534542
13 29963613
14 .....

Now i want filter all row from 1 -> 13 and highlight specific row only when sum row hightlight = C1
Example : B2+B3+B6 = C1
Now B2,B3,B6 highlight
I using formula on Conditional Formatting look like: =SUM($B$1:B1)=$C$1. But it not working. How to highlight rows only sum value equal specific value ?

 

[Skyybot]

I don't think this is possible. There could be multiple solutions. Let's say I have a Total in C1 = 9. I could get it with 2 + 2 + 5, or 1 + 1 + 7, or 0 + 0 + 9. If any of these numbers existed in Column B, how would the program know which specific values I was looking for? Now, you may be able to create a Macro which loops through Column B and highlights the first solution it runs across but there still may be other solutions existing in the Column so I don't know if that would exactly solve your problem.

 

[trungtb]

I don't think this is possible. There could be multiple solutions. Let's say I have a Total in C1 = 9. I could get it with 2 + 2 + 5, or 1 + 1 + 7, or 0 + 0 + 9. If any of these numbers existed in Column B, how would the program know which specific values I was looking for? Now, you may be able to create a Macro which loops through Column B and highlights the first solution it runs across but there still may be other solutions existing in the Column so I don't know if that would exactly solve your problem.

I think if we get it with 2 + 2 +5 and then we ignore another result . I only know what row on column B sum value equal C1. If we have mutiple row , please ignore. When i research , another suggest i using resolver. When i using resolver , result not correct. If you have a macro or another solution , please share with me . Thanks you so much

 

[Skyybot]

Try this on a copy of your Workbook as unexpected result may occur. This is a VBA solution. I'm not able to do this via Workbook Functions.

Sub fndSum()
Dim wb As Workbook, ws As Worksheet, rng As Range, cell As Range
Dim num1 As Long, num2 As Long, num3 As Long, fndNum As Long, i As Long
Dim j As Long, k As Long
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set rng = ws.Columns(2)
fndNum = ws.Range("C1").Value
For i = 1 To ws.UsedRange.Rows.Count
    num1 = rng.Cells(i, 1).Value
        For j = i + 1 To ws.UsedRange.Rows.Count
            num2 = rng.Cells(j, 1).Value
                For k = j + 1 To ws.UsedRange.Rows.Count
                    num3 = rng.Cells(k, 1).Value
                    Select Case fndNum
                        Case num1
                            Debug.Print ws.Cells(i, 2).Address
                            ws.Cells(i, 2).Interior.Color = RGB(255, 0, 0)
                            Exit Sub
                        Case num1 + num2
                            Debug.Print ws.Cells(i, 2).Address & "," & ws.Cells(j, 2).Address
                            ws.Cells(i, 2).Interior.Color = RGB(255, 0, 0)
                            ws.Cells(j, 2).Interior.Color = RGB(255, 0, 0)
                            Exit Sub
                        Case num1 + num3
                            Debug.Print ws.Cells(i, 2).Address & "," & ws.Cells(k, 2).Address
                            ws.Cells(i, 2).Interior.Color = RGB(255, 0, 0)
                            ws.Cells(k, 2).Interior.Color = RGB(255, 0, 0)
                            Exit Sub
                        Case num2 + num3
                            Debug.Print ws.Cells(j, 2).Address & "," & ws.Cells(k, 2).Address
                            ws.Cells(j, 2).Interior.Color = RGB(255, 0, 0)
                            ws.Cells(k, 2).Interior.Color = RGB(255, 0, 0)
                            Exit Sub
                        Case num1 + num2 + num3
                            Debug.Print ws.Cells(i, 2).Address & "," & ws.Cells(j, 2).Address _
                            & "," & ws.Cells(k, 2).Address
                            ws.Cells(i, 2).Interior.Color = RGB(255, 0, 0)
                            ws.Cells(j, 2).Interior.Color = RGB(255, 0, 0)
                            ws.Cells(k, 2).Interior.Color = RGB(255, 0, 0)
                            Exit Sub
                    End Select
                Next k
        Next j
Next i
End Sub