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 two-column worksheet:
Team Win/Loss
------------------------
1 W
2 L
1 W
3 W
1 L
I 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 two-column 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