Excel conditional sum

B

bloop

I have a two colums with values, A contains numbers from 1 to 10, B contains
numerique values. Now I would like to make the sum of the values in B
depending on the value in A, e.g. sum of all values in B fot which the
content in A < 5
 
R

Ron Coderre

Try something like this:

C1: =SUMIF(A:A,"<5",B:B)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

Roberto

=SUMPRODUCT(--(A1:A10<5),--(B1:B10))

Roberto

"bloop" <[email protected]> ha scritto nel messaggio
:I have a two colums with values, A contains numbers from 1 to 10, B contains
: numerique values. Now I would like to make the sum of the values in B
: depending on the value in A, e.g. sum of all values in B fot which the
: content in A < 5
 
B

Bob Phillips

No, no. Ron gave you the proper answer.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Roberto

"Bob Phillips" <[email protected]> ha scritto nel messaggio
: No, no. Ron gave you the proper answer.
:
Just to improve my Excel: why SUMIF is better than SUMPRODUCT?
Roberto
 
B

Bob Phillips

Because it is optimised for conditional counting, so when you only have one
condition, use SUMIF. SUMPRODUCT is good for multiple conditions, but the --
is coercing a TRUE/FALSE result to 1/0 so that it can do the product can be
executed. It also looks more intuitive, no --.

I just did a quick test, and in this SUMIF was quicker than SUMPRODUCT by a
factor of some 42%.

That is why it is better in the right circumstances.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

Ashish Mathur

Hi,

You may use a sum(if( array formula (Ctrl+Shift+Enter)

=sum(if(rangeA<=5,rangeB))

Regards,
 
R

Roberto

Thanks for your answer, Bob.
Roberto

"Bob Phillips" <[email protected]> ha scritto nel messaggio
: Because it is optimised for conditional counting, so when you only have one
: condition, use SUMIF. SUMPRODUCT is good for multiple conditions, but the --
: is coercing a TRUE/FALSE result to 1/0 so that it can do the product can be
: executed. It also looks more intuitive, no --.
:
: I just did a quick test, and in this SUMIF was quicker than SUMPRODUCT by a
: factor of some 42%.
:
: That is why it is better in the right circumstances.
:
: --
: HTH
:
: Bob Phillips
:
: (remove nothere from email address if mailing direct)
:
: "Roberto" <schebobchiocciolatinpuntoit> wrote in message
: : >
: > "Bob Phillips" <[email protected]> ha scritto nel
: messaggio
: > : > : No, no. Ron gave you the proper answer.
: > :
: > Just to improve my Excel: why SUMIF is better than SUMPRODUCT?
: > Roberto
: >
: >
:
:
 
Top