sum only certain figures in the column

  • Thread starter Thread starter Jack B
  • Start date Start date
J

Jack B

Re Excel 97 --------

I want to SUM only the figures in J20:80 (put sum in J81) that do not have a
blank corresponding cell in K20:80, but I can't figure out the IF statement.
To clarify, if for instance, K25 is blank, then I don't want J25 included in
the sum in J81.


Jack
 
Peo & David -- Thanks! It works, but I don't understand why that works.

SUMPRODUCT gives the product of the corresponding cells then adds all the
products, so I don't see how this works. If, for instance, K26 is not
blank, this part (K20:K80<>"") of the formula seems to be saying "use K26 as
a multiplier," so I'm confused.


Jack

----------------------------------------------






"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
=SUMPRODUCT(--(K20:K80<>""),J20:J80)
or
=SUMPRODUCT((K20:K80<>"")*J20:J80)
 
This is a bit more intuitive, and works, as long as you don't have formulas
in Column K that return nulls ( "" ):

=SUMIF(K20:K80,"<>",J20:J80)
--

HTH,

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


Peo & David -- Thanks! It works, but I don't understand why that works.

SUMPRODUCT gives the product of the corresponding cells then adds all the
products, so I don't see how this works. If, for instance, K26 is not
blank, this part (K20:K80<>"") of the formula seems to be saying "use K26 as
a multiplier," so I'm confused.


Jack

----------------------------------------------






"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
=SUMPRODUCT(--(K20:K80<>""),J20:J80)
or
=SUMPRODUCT((K20:K80<>"")*J20:J80)
 
David,

Thanks for the explanation.


Jack

----------------------------------


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
What it is using as a multiplier is the boolean (logical) condition that K26
is not equal to the empty string "".
If K26 is not blank, the condition is TRUE; if K26 is blank, the condition
is FALSE.
When multiplied, TRUE is treated as 1, and FALSE is treated as 0.
Hence when K26 is not blank, =(K26<>"")*J26 will give TRUE*J26, which is
1*J26, hence J26,
while when K26 is blank, =(K26<>"")*J26 will give FALSE*J26, which is 0*J26,
hence 0.
--
David Biddulph

Jack said:
Peo & David -- Thanks! It works, but I don't understand why that
works.

SUMPRODUCT gives the product of the corresponding cells then adds all
the products, so I don't see how this works. If, for instance, K26
is not blank, this part (K20:K80<>"") of the formula seems to be
saying "use K26 as a multiplier," so I'm confused.

Jack

----------------------------------------------

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
=SUMPRODUCT(--(K20:K80<>""),J20:J80)
or
=SUMPRODUCT((K20:K80<>"")*J20:J80)

Jack said:
Re Excel 97 --------

I want to SUM only the figures in J20:80 (put sum in J81) that do not
have a blank corresponding cell in K20:80, but I can't figure out the
IF statement. To clarify, if for instance, K25 is blank, then I don't
want J25 included in the sum in J81.


Jack
Click to expand...
Click to expand...
 
RD --

Good to know -- thanks for that.


Jack
---------------------------------


This is a bit more intuitive, and works, as long as you don't have formulas
in Column K that return nulls ( "" ):

=SUMIF(K20:K80,"<>",J20:J80)
--

HTH,

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


Peo & David -- Thanks! It works, but I don't understand why that works.

SUMPRODUCT gives the product of the corresponding cells then adds all the
products, so I don't see how this works. If, for instance, K26 is not
blank, this part (K20:K80<>"") of the formula seems to be saying "use K26 as
a multiplier," so I'm confused.


Jack

----------------------------------------------






"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
=SUMPRODUCT(--(K20:K80<>""),J20:J80)
or
=SUMPRODUCT((K20:K80<>"")*J20:J80)
 
You're welcome, and appreciate the feed-back.
--

Regards,

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

RD --

Good to know -- thanks for that.


Jack
---------------------------------


This is a bit more intuitive, and works, as long as you don't have formulas
in Column K that return nulls ( "" ):

=SUMIF(K20:K80,"<>",J20:J80)
--

HTH,

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


Peo & David -- Thanks! It works, but I don't understand why that works.

SUMPRODUCT gives the product of the corresponding cells then adds all the
products, so I don't see how this works. If, for instance, K26 is not
blank, this part (K20:K80<>"") of the formula seems to be saying "use K26 as
a multiplier," so I'm confused.


Jack

----------------------------------------------






"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
=SUMPRODUCT(--(K20:K80<>""),J20:J80)
or
=SUMPRODUCT((K20:K80<>"")*J20:J80)
 
Back
Top