Thread: Need Validation type drop-down wildcard in Sumproduct

I have a worksheet report that counts question answers filtered by facility, date range and case manager. I use Data Validation "List" criteria to make the in-cell drop-down lists for the criteria of the variable values. For most of them the selected value works fine, but for the case manager drop-down I need a value in the list, other than the peoples names, to represent "All" or be a wildcard and include all values in the selection.

Here is the formula:

Code:

=SUMPRODUCT(--(WTP!$P$2:$P$420='WTP Report'!F$4),--(WTP!$I$2:$I$420='WTP Report'!$I$1),--(WTP!$M$2:$M$420='WTP Report'!$M$2),--(OFFSET(WTP!$A$2:$A$420,0,MATCH($Q6,WTP!$1:$1,FALSE)-1)=E$5)*1)

Here is the piece of concern from the above formula:

Code:

(WTP!$M$2:$M$420='WTP Report'!$M$2)

WTP Report!$M$2 is where the drop-down list is located. It gets it's list from a range containing a bunch of peoples names. Say, $AA$2:$AA$32
WTP!$M$2:$M$420 is where the names from the records in the database are stored.

I'd like to be able to stick some value like % or * or "%*%" in the list of names that would act like a "All" wildcard. I tried those, they don't work. ;-)

Thanks in advance.