G
Guest
I want to use the SUMPRODUCT function in my code. The function, as I would
write it in an Excel cell, is as follows.
=IF(SUMPRODUCT((series=Sheet1!$A2)*(status="Open"))=0,"Done","Pending Action")
Where "series" and "status" are ranges of cells (both single column, 10,000
rows) that have been named.
I've tried using the follow variations of VB code, but I keep getting a
run-time error '13' message: "Type mismatch."
Application.WorksheetFunction.SumProduct((Range("series") = Cells(1,
1).Value) * (Range("status") = "Open"))
Application.WorksheetFunction.SumProduct((Array("series") = Cells(1,
1).Value) * (Array("status") = "Open"))
I've stopped iterating through other possible variations to save myself time
and frustration. Does anyone know the correct arguments to get the desired
result? My aim is to look at a large tabular dataset, determine if the
"status" of any items corresponding to a specified identifier is "open", then
execute other code accordingly. So if sumproduct = 0 do x, else do y.
Thanks in advance for your help!
write it in an Excel cell, is as follows.
=IF(SUMPRODUCT((series=Sheet1!$A2)*(status="Open"))=0,"Done","Pending Action")
Where "series" and "status" are ranges of cells (both single column, 10,000
rows) that have been named.
I've tried using the follow variations of VB code, but I keep getting a
run-time error '13' message: "Type mismatch."
Application.WorksheetFunction.SumProduct((Range("series") = Cells(1,
1).Value) * (Range("status") = "Open"))
Application.WorksheetFunction.SumProduct((Array("series") = Cells(1,
1).Value) * (Array("status") = "Open"))
I've stopped iterating through other possible variations to save myself time
and frustration. Does anyone know the correct arguments to get the desired
result? My aim is to look at a large tabular dataset, determine if the
"status" of any items corresponding to a specified identifier is "open", then
execute other code accordingly. So if sumproduct = 0 do x, else do y.
Thanks in advance for your help!