1)

=SUMPRODUCT(--(C2:C1180=L10))-SUMPRODUCT((C2:C1180=L10)*(LEFT(J2:J1180,1)={"a"," m","d"}))

2)

In another column, use a formula like

=IF(SUMPRODUCT(--(LEFT(J2,1)={"a","m","d"}))=0,IF(COUNTIF($J$2:J2,J 2)=1,J2,""),"")

then copy down, and use filters to show non-blamk values.

HTH,
Bernie
"Twishlist" wrote in message
...
Just rec'd speedy and really helpful answers regarding my Q on multiple
criteria, but I should have also asked the correct formula for quantifying
values OTHER THAN nominated criteria:
1. if J2:J1180 contains data OTHER than "a", "m", "d", how do I adjust
the
following formula to calculate this?
=SUMPRODUCT((C2:C1180=L10)*(LEFT(J2:J1180)={"a","m ","d"}))
2. Is there a way of displaying (identifying) what that OTHER data may be
in a column other that the criteria specified? That is, returning
whatever
info that doesn't fit the given criteria. How wd I tweak the following
formula to do this (if it is at all possible)?
=SUMPRODUCT((C2:C1180=L10)*(LEFT(J2:J1180)={"a","d ssn"}))