Thanks Julie. Your suggestion looks good but I was hoping to be able to
evaluate a much larger number of criteria and to use "*" in any number of the
fields as needed. Writing a formula to account for all the possiblities would
be difficult.

Any other suggestions?

"JulieD" wrote:

Hi Jim

you can't use wildcards with SUMPRODUCT ... so the best solution which i
think (and i'm sure someone will correct me if i'm wrong) will give you max
flexibility is:

=IF(A6="*",SUMPRODUCT(--(B1:B3=B6),--(C1:C3=C6)),SUMPRODUCT(--(A1:A3=A6),--(B1:B3=B6),--(C1:C3=C6)))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Jim" wrote in message
...
I would like to use a wildcard character with a sumproduct formula.
Data
1 A B C
2 Joe CA 01
3 Sue MA 01
4 Dan CA 02

6 * CA 01
=Sumproduct((A2:A4=A6)*(B2:B4=B6)*(C2:C4=C6))
With "*" in cell A6 I expect to get a result of 1 but instead I get 0. Any
suggestions?
Thanks