sumif

[Harvey Waxman]

I have two columns of numbers. The difference between each pair can be greater
than zero, less than zero or zero.

I'd like to find the total of all the greater than zeros without actually
having a column of the differences, if possible.

Is it doable?

 

[JE McGimpsey]

I have two columns of numbers. The difference between each pair can be
greater
than zero, less than zero or zero.

I'd like to find the total of all the greater than zeros without actually
having a column of the differences, if possible.

One way:

=SUMPRODUCT(--(A1:A100>B1:B100),A1:A100-B1:B100)

 

[Harvey Waxman]

One way:

=SUMPRODUCT(--(A1:A100>B1:B100),A1:A100-B1:B100)

Thanks, what about less than zero's? I tried variations with no success
because the logic escapes me at first blush.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A100>B1:B100),A1:A100-B1:B100)

Thanks, what about less than zero's? I tried variations with no success
because the logic escapes me at first blush.[/QUOTE]

One way:

=SUMPRODUCT(--(A1:A100<B1:B100),A1:A100-B1:B100)

or, if you want to have the absolute value:

=-SUMPRODUCT(--(A1:A100<B1:B100),A1:A100-B1:B100)

You could do the latter slightly more efficiently as

=SUMPRODUCT(-(A1:A100<B1:B100),A1:A100-B1:B100)

 

[Harvey Waxman]

Thanks, what about less than zero's? I tried variations with no success
because the logic escapes me at first blush.

One way:

=SUMPRODUCT(--(A1:A100<B1:B100),A1:A100-B1:B100)

or, if you want to have the absolute value:

=-SUMPRODUCT(--(A1:A100<B1:B100),A1:A100-B1:B100)

You could do the latter slightly more efficiently as

=SUMPRODUCT(-(A1:A100<B1:B100),A1:A100-B1:B100)[/QUOTE]

Once again, thanks

 

[Harvey Waxman]

can you refresh the explanation for "--" ?

 

[JE McGimpsey]

can you refresh the explanation for "--" ?

See
http://www.mcgimpsey.com/excel/doubleneg.html

 

[Harvey Waxman]

See
http://www.mcgimpsey.com/excel/doubleneg.html

Will I ever learn to check your website first? Thanks