I have this code in VBA,

value1 = "Product1"
Value4 = " "

Range("D8").Formula = Application.Evaluate("SumProduct(--(G2:G10000 = """ & value1 & """) , --(C2:C10000 <> """ & Value4 & """))")

in above code instead of specifying the range manually (i.e. instead of G2:G10000) I want to specify dynamic range based on header value in row 1.

I believe for that I want to insert below formula but I don't how to merge these both formula, any suggestion?

Application.Index(Range("A2:BB10000"), 0, Application.WorksheetFunction.Match("Product", Range("A1:BB1"), 0))

Thanks in advance.
-Anand