Sumproduct - formula to return data OTHER than given criteria

[Twishlist]

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","dssn"}))

 

[Bernie Deitrick]

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,J2)=1,J2,""),"")

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

HTH,
Bernie

 

[T. Valko]

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"}))

So, you want to count all cells that *are not* a, m or d?

=SUMPRODUCT(--(C2:C1180=L10),--(ISNA(MATCH(LEFT(J2:J1180),{"a","m","d"},0))))

That will also count empty/blank cells because they meet the condition of
*not being* either a, d or m. To exclude possible empty/blank cells:

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","dssn"}))

Not sure I follow you on that. You want to list all cells in J2:J1180 that
don't start with a, m or d?

 

[Twishlist]

Thank you for the first answer, I'll try it shortly and confirm.
With regard to the second question,: Ideally, the formula would produce a
list of all item codes featured in j2:j1180 that fall outside the nominated
criteria. The problem arises that when I specify criteria, such as
commencing with "a", there may be other item codes in this column of which
I'm unaware (until my numbers don't correspond). Then I can only locate them
by perusing manually.

 

[T. Valko]

Ideally, the formula would produce a list of all item codes

featured in j2:j1180 that fall outside the nominated criteria

One way to do that is to filter the data. See Bernie's reply where he
explains that.