SUMPRODUCT Formula Help
 

I have entered the following formula and I have an issue where it will not
count the "21" entries. Can the formula work with alpha and numerics used in
the same column? If so, what am I doing wrong?

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001))

SUMPRODUCT Formula Help
 

Assuming the "21" values are actually numbers, not text, try:

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21), --(C9:C10001="D"),
E9:E10001)

In article ,
CLM wrote:

I have entered the following formula and I have an issue where it will not
count the "21" entries. Can the formula work with alpha and numerics used in
the same column? If so, what am I doing wrong?

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001))

SUMPRODUCT Formula Help
 

CLM,

What are you trying to do with the first part of the equation? It looks
like you're trying to add the array with itself. Per the Help the SUMPRODUCT
function treats nonnumeric entries as if they were zeros. The second part of
the equation actually works because you're multiplying the two arrays
together. Since you're doing this the "D" entries are treated like a value
of "1". Are you getting any answer other than "0" with this equation because
that is all I get as when I tried it out?
--
Tim Murphy


"JE McGimpsey" wrote:

Assuming the "21" values are actually numbers, not text, try:

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21), --(C9:C10001="D"),
E9:E10001)

In article ,
CLM wrote:

I have entered the following formula and I have an issue where it will not
count the "21" entries. Can the formula work with alpha and numerics used in
the same column? If so, what am I doing wrong?

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001))

SUMPRODUCT Formula Help
 

Since SUMPRODUCT is an array formula (even though it doesn't require
CTRL-SHIFT-ENTER),

(A9:A10001="ZQ")+(A9:A10001=21)

will return a 9992 element array of ones and zeros - 1 if the
corresponding cell contains either "ZQ" or 21, 0 if not.

Internally, each term returns an array of TRUE/FALSE, but XL coerces
TRUE/FALSE to 1/0 when using a math operator like "+".




In article ,
vttanker wrote:

CLM,

What are you trying to do with the first part of the equation? It looks
like you're trying to add the array with itself. Per the Help the SUMPRODUCT
function treats nonnumeric entries as if they were zeros. The second part of
the equation actually works because you're multiplying the two arrays
together. Since you're doing this the "D" entries are treated like a value
of "1". Are you getting any answer other than "0" with this equation because
that is all I get as when I tried it out?
--
Tim Murphy


"JE McGimpsey" wrote:

Assuming the "21" values are actually numbers, not text, try:

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21), --(C9:C10001="D"),
E9:E10001)

In article ,
CLM wrote:

I have entered the following formula and I have an issue where it will
not
count the "21" entries. Can the formula work with alpha and numerics
used in
the same column? If so, what am I doing wrong?

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001))

SUMPRODUCT Formula Help
 

Jim, CLM,

I didn't look at it that way, but I agree. As written below it works.

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21),(C9:C1 0001="D")*(E9:E10001))
--
Tim Murphy


"JE McGimpsey" wrote:

Since SUMPRODUCT is an array formula (even though it doesn't require
CTRL-SHIFT-ENTER),

(A9:A10001="ZQ")+(A9:A10001=21)

will return a 9992 element array of ones and zeros - 1 if the
corresponding cell contains either "ZQ" or 21, 0 if not.

Internally, each term returns an array of TRUE/FALSE, but XL coerces
TRUE/FALSE to 1/0 when using a math operator like "+".




In article ,
vttanker wrote:

CLM,

What are you trying to do with the first part of the equation? It looks
like you're trying to add the array with itself. Per the Help the SUMPRODUCT
function treats nonnumeric entries as if they were zeros. The second part of
the equation actually works because you're multiplying the two arrays
together. Since you're doing this the "D" entries are treated like a value
of "1". Are you getting any answer other than "0" with this equation because
that is all I get as when I tried it out?
--
Tim Murphy


"JE McGimpsey" wrote:

Assuming the "21" values are actually numbers, not text, try:

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21), --(C9:C10001="D"),
E9:E10001)

In article ,
CLM wrote:

I have entered the following formula and I have an issue where it will
not
count the "21" entries. Can the formula work with alpha and numerics
used in
the same column? If so, what am I doing wrong?

=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001))