Sub abc()
Dim MyVal, MyFormula As String
MyVal = Application.InputBox("Please enter the name you want to calculate", "", "Chetan etc.")
If MyVal = False Then
MsgBox "Macro Cancelled. Macro will now exit", vbCritical, ""
Exit Sub
ElseIf Len(MyVal) = 0 Or MyVal = "Chetan etc." Or MyVal = "" Then
MsgBox "Blank value entered. Macro will now exit", vbCritical, ""
Exit Sub
Else
MyFormula = "=SUMPRODUCT((A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & "=""" & MyVal & """)*(B1:B" & Cells(Rows.Count, "B").End(xlUp).Row & "))"
MsgBox Evaluate(MyFormula), vbInformation, ""
End If
End Sub