Posts by jpslav


Thanks Doug, but is there a way to pass "teamname" and "outcome" into the VBA function so that SUMPRODUCT can use it? I want the range to be something that is passed in to the function so that I can use the function for different sets of data.
Thanks, JP

Howdy,
I'm trying to make an Excel VBA function that generalizes the SUMPRODUCT worksheet function. This new function would take two named ranges and two conditions on them and then return the count of rows that meet those conditions
For example, imagine that I have the following twocolumn worksheet:
Team Win/Loss

1 W
2 L
1 W
3 W
1 LI then name the first column's data as "TEAMNAME" and the second column's data as "OUTCOME".
Using SUMPRODUCT directly, I could write
=SUMPRODUCT((TEAMNAME="1")*(OUTCOME="W"))
to see that team 1 won twice.
However, I want to write a VBA function that will take these ranges and conditions and return this sumproduct value,e.g.
=MYFUNC(TEAMNAME,"1",OUTCOME,"W")
I want to generalize in this way because I have many different twocolumn pieces of data where I want to use the same centralized code (and in truth my real problem has much more data than this example). The problem is that I'm having trouble passing the named ranges "TEAMNAME" and "OUTCOME" to my VBA SUMPRODUCT function and getting it to do the counting. Any suggestions or examples that work?
Many thanks, JP