Add values in a column according to value in another column

[Paul Kaye]

How can I add the values in a column according to values in another
column? If there is any value in a row in column B, I want to include
the value of the corresponding row in column A. I'm flexible as to
whether this is ANY value (i.e. not empty) or greater than zero.

 

[Cimjet]

Hi Paul
Maybe something like this =IF(B1="","",IF(B1>0,B1+A1))
Regards
Cimjet

 

[Gary''s Student]

=SUMPRODUCT(A1:A100,--(B1:B100<>"")) adjust the range to suit.

 

[Paul Kaye]

=SUMPRODUCT(A1:A100,--(B1:B100<>"")) adjust the range to suit.

What does the -- and <> do? I really don't understand that formula!

 

[Paul Kaye]

Hi Paul
Maybe something like this =IF(B1="","",IF(B1>0,B1+A1))
Regards

The trouble is that it needs to refer to all the rows in a range.

 

[Paul Kaye]

What does the -- and <> do? I really don't understand that formula!

Sorry Gary's Student - I forgot to say that it worked perfectly, I
just can't understand why!!! Could you give me a brief description?

 

[Gary''s Student]

It just means "not equal to"

 

[Dave Peterson]

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html