Sumproduct Help

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

=sumproduct((g23:g5000=6.5)*(q23:q5000))
=sumproduct((g23:g5000=16)*(q23:q5000))
=sumproduct((g23:g5000=3.4)*(q23:q5000))

All of the above formulas work individually, but I I want to combine
them into one formula, so I can have the totals for all of these
different instances in one cell, but I'm having trouble coming up with
the proper structure.

I'm interested in learning the technique, so I can do it myself in the
future. There must be an easy way to think about this.

Thanks
 
Do you want the total of the three?...then one way:

=sumproduct((g23:g5000=6.5)*(q23:q5000))+sumproduct((g23:g5000=16)*(q23:q5000))+sumproduct((g23:g5000=3.4)*(q23:q5000))

HTH,
Paul
 
An alternative:

=SUMPRODUCT((G23:G5000=6.5)+(G23:G5000=16)+(G23:G5000=3.4),Q23:Q5000)
 
And just another more concise alternative:

=SUMPRODUCT((G23:G5000={6.5,16,3.4})*Q23:Q5000)
 
One difference between this one and the longer one that I provided is
that any non-numeric value in Q3:Q5000 (e.g., a space character used to
"clear" a cell) in the shorter version leads to a #VALUE! error, since
the multiplication is done by the * operator, rather than by
SUMPRODUCT().

With the somewhat longer version, SUMPRODUCT() ignores the text (well,
treats it as zero).

Don't know if that's relevant to the OP's situation, obviously.
 
You may or may not remember John, our conversations in years past,
concerning my extolling the virtues of the "error checking" capabilities of
the asterisk form, where the calculating range is supposed to be exclusively
numeric, and my distaste for the unary form for "passing over" any incorrect
entries into this range, and returning inaccurate returns.

You're absolutely right though, if the calculating range is populated by
formulas which may return nulls ( "" ) or text messages.

If importing into the calculating range or keying in values, I feel the
asterisk should be the method of choice. It insures the veracity of the
data.
As you mentioned the possibility of keying in spaces ... I feel that an
error message would be appropriate in such a case ... to educate users in
it's unacceptable (bad habit) consequences.

I agree with you that our ignorance of the OP's situation makes any
statement beyond our grasp, as to what's absolutely "right" or "wrong".

Funny, this started out as an example of an array constant and evolved into
my diatribe of the unary!<bg>
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

One difference between this one and the longer one that I provided is
that any non-numeric value in Q3:Q5000 (e.g., a space character used to
"clear" a cell) in the shorter version leads to a #VALUE! error, since
the multiplication is done by the * operator, rather than by
SUMPRODUCT().

With the somewhat longer version, SUMPRODUCT() ignores the text (well,
treats it as zero).

Don't know if that's relevant to the OP's situation, obviously.
 
Back
Top