sumproduct with three criteria

[BobA]

I have the following formula in a cell, but it is returning a $0.00 value. What am I doing wrong?

=SUMPRODUCT(--(C11:C1000=J2),(D111000=I2),(F11:F1000))

 

[Claus Busch]

Hi Bob,

Am Tue, 16 Jul 2013 13:23:16 -0700 (PDT) schrieb BobA:

I have the following formula in a cell, but it is returning a $0.00 value. What am I doing wrong?

=SUMPRODUCT(--(C11:C1000=J2),(D111000=I2),(F11:F1000))

try:
=SUMPRODUCT(--(C11:C1000=J2),--(D111000=I2),F11:F1000)


Regards
Claus B.

 

[joeu2004]

What am I doing wrong?
=SUMPRODUCT(--(C11:C1000=J2),(D111000=I2),(F11:F1000))

First, that sums F11:F1000 based on only two criteria, not three. Did you
mean for that last term to be a conditional expression, e.g. F11:F1000=K2?

Second, what you might have done wrong is omit the double-negative (--)
before the second term, just as you have before the first term. Or perhaps
you intended to write:

=SUMPRODUCT((C11:C1000=J2)*(D111000=I2),F11:F1000)
or
=SUMPRODUCT((C11:C1000=J2)*(D111000=I2)*(F11:F1000=K2))

To explain.... C11:C1000=J2, for example, returns an array of logic values,
TRUE or FALSE. SUMPRODUCT "treats array entries that are not numeric as if
they were zeros". Consequently, you are multiplying everything by zero,
which explains why SUMPRODUCT returns zero.

TRUE and FALSE are converted to the values 1 and 0 when they are included in
an arithmetic expression. That is why we might write --(C11:C1000=J2): the
double-negative is (two) arithmetic operations.

Likewise, (C11:C1000=J2)*(D111000=I2) is one arithmetic operation
(multiply). So writing double-negative (--) in front of that, as some
people do, is unnecessary (and costly).

Nonetheless, alternatively you can write:

=SUMPRODUCT(--(C11:C1000=J2),--(D111000=I2),F11:F1000)
or
=SUMPRODUCT(--(C11:C1000=J2),--(D111000=I2),--(F11:F1000=K2))

 

[BobA]

I have the following formula in a cell, but it is returning a $0.00 value. What am I doing wrong?



=SUMPRODUCT(--(C11:C1000=J2),(D111000=I2),(F11:F1000))

Thanks for the help, but every suggestion returns a zero value. Maybe I'm going about it wrong. Let me try and explain it a different way:

C1=Tom
D1=Red
F1=$100

J2=Tom
I2=Red

I want to write a formula that finds all examples of Tom and Red (in the C and D columns)and totals their sums from the F column.

 

[joeu2004]

Thanks for the help, but every suggestion returns a zero value.
Maybe I'm going about it wrong.

No.

Let me try and explain it a different way:
C1=Tom
D1=Red
F1=$100
J2=Tom
I2=Red
I want to write a formula that finds all examples of
Tom and Red (in the C and D columns)and totals their
sums from the F column.

And you did. You just have a syntax error. It should be:

=SUMPRODUCT(--(C11:C1000=J2),--(D111000=I2),F11:F1000)
or
=SUMPRODUCT((C11:C1000=J2)*(D111000=I2),F11:F1000)

As for why the sum is zero, there could be many other problems to deal with.

First, what does =ISTEXT(F11) return for each of F11 through F1000? If it
returns TRUE, that is a problem.

Second, if C11 displays "Tom", what does =C11=J2 return? Likewise, if D11
displays "Red", what does =D11=I2 return? If either returns FALSE, that is
a problem.

If you cannot debug your problem, I suggest that you post the URL of an
example Excel file uploaded to a file-sharing website. The following is a
list of some file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

 

[isabelle]

hi BobA,

this is a matricial formula, have you validate it with ctrl + maj + enter

isabelle


Le 2013-07-16 22:05, BobA a écrit :

 

[isabelle]

opp sorry, forget that, this is not an array formula

isabelle

Le 2013-07-17 01:02, isabelle a écrit :

 

[BobA]

"As for why the sum is zero, there could be many other problems to deal with...

Second, if C11 displays "Tom", what does =C11=J2 return? Likewise, if D11
displays "Red", what does =D11=I2 return? If either returns FALSE, that is
a problem."

Thank you, I found the problem. Somehow, cell I2 was formatted for a fraction, not text. I changed it and now everything works.

All help is much appreciated.

 

[joeu2004]

[joeu2004 wrote:]

Second, if C11 displays "Tom", what does =C11=J2 return?
Likewise, if D11
displays "Red", what does =D11=I2 return? If either
returns FALSE, that is a problem."

Thank you, I found the problem. Somehow, cell I2 was formatted
for a fraction, not text. I changed it and now everything works.

I'm glad that everything works now. But I do not believe that simply
changing the cell format is the reason.

It is not necessary for the cell format to be Text in order to put
(non-numeric) text into the cell.

To demonstrate, try the following experiment in a new workbook (Excel file).

Format A1 as Text (not really necessary). Format B1 as Fraction. Enter
"Tom" without quotes into both A1 and B1. Enter the following formulas:

C1: =(A1=B1)
D1: =SUMPRODUCT(--(A1=B1))

The first will return TRUE. The second with return 1.