sumproduct

  • Thread starter Thread starter John
  • Start date Start date
J

John

three columns: H,D,C
every entry in D and C is a number
entries in H, which are not numbers, are blanks

the problem is to sum the product of entries in C and D that correspond
to blank entries in H

SUMPRODUCT(if(isblank(H3:H20),1,0)*C3:C20*D3:D20) computes an answer,
but it is wrong; it sums the unconditional product of entries in C and
D

inserting the above into function computes an answer, and it is correct

why doesn't my formula compute correctly?

John
 
John wrote...
three columns: H,D,C
every entry in D and C is a number
entries in H, which are not numbers, are blanks

the problem is to sum the product of entries in C and D that correspond
to blank entries in H

SUMPRODUCT(if(isblank(H3:H20),1,0)*C3:C20*D3:D20) computes an answer,
but it is wrong; it sums the unconditional product of entries in C and
D

inserting the above into function computes an answer, and it is correct

why doesn't my formula compute correctly?
Click to expand...

when you want an array result from the IF function, you *MUST* enter
the formula calling it as an array formula. IF is fussy about that.

Rewrite your formula as

=SUMPRODUCT(ISBLANK(H3:H20)*C3:C20*D3:D20)
 
QC Coug,

Your solution works. Thanks. Two questions: (1) What does the -- in
(--(H9:H24="") do? (2) How do I compute the conditional sum
corresponding to non-blank entries in H?

It seems inelegant to subtract the conditional sum, computed above,
from SUMPRODUCT(C3:C20*D3:D20).

John
 
Back
Top