Sumproduct - formula to return data OTHER than given criteria
 

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

Sumproduct - formula to return data OTHER than given criteria
 

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

Sumproduct - formula to return data OTHER than given criteria
 

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:

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

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

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?

--
Biff
Microsoft Excel MVP


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

Sumproduct - formula to return data OTHER than given criteria
 

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" wrote:

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:

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

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

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?

--
Biff
Microsoft Excel MVP


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

Sumproduct - formula to return data OTHER than given criteria
 

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.

--
Biff
Microsoft Excel MVP


"Twishlist" wrote in message
...
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" wrote:

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:

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

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

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?

--
Biff
Microsoft Excel MVP


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