how to add "optional" parameters to SUMPRODUCT?

[ybolotin]

I have something like this, which gives me the count of all the cells that match whatever's specified in J3:L3
=SUMPRODUCT((B:B=J3)*(C:C=K3)*(D:D=L3))

(the real formula has quite a bit more values/columns in it, this was a proof-of-concept sample)

however, I would really like to be able to optionally exclude one (or more) of these parameters, preferably without changing the formula

replacing the look-for value with * or ? doesn't work (it's looking for the literal "*", not any string)
while it's theoretically possible to implement the negative-lookup list (e.g. <>"", <>"1", etc) some of the fields have multiple possible text values, where I want to count either one specific one, or all-rows-including-missing (i.e. ignore this column for comparison purposes)

how can I do this, if at all?


so what I want is a simple way to change the lookup from "count all the rows where these 15 values perfectly match the desired output" to "count all the rows where X of the values perfectly match the X I want, regardless of what's in the rest", where X is between 2 and 15 inclusive.

doing this without having to add formula rows to every value would also be awesome

 

[ybolotin]

some too-late text fixes:
counts all the *rows* that match the required pattern

"without changing the formula" = without changing it each time I want to make a change. I want to make this as seamless for an end-user as possible (so selecting "ALL" from a dropdown would stop filtering by that column, that sort of thing).

VBA/macros is out of the question too, since some of the users will not even have permissions to enable macros.

 

[ybolotin]

stole this from barry houdini in http://www.mrexcel.com/forum/excel-questions/830425-stuck-simple-problem-sumif-sumproduct.html


looks like I can do this with COUNTIFS instead (for the CONDITION parameter, put in "<>INVALIDVALUE" to count everything, "<>" to count non-missing, or a specific value to count that value)