calculate cells between two values

[Samantha]

how do you calculate cells between two values e.g. in column A there ar
random numbers between 1 and 100, in column B there are also rando
numbers between 1 and 300. what formula do I use to have excell searc
for numbers greater or equal to 30 but less than or equal to 61, i
found, it must add up the numbers in column B

 

[Frank Kabel]

Hi
one way:
=SUMPRODUCT((A1:A100>=30)*(A1:A100<=61)*(B1:B100))

HTH
Frank

 

[Paul]

how do you calculate cells between two values e.g. in column A there are
random numbers between 1 and 100, in column B there are also random
numbers between 1 and 300. what formula do I use to have excell search
for numbers greater or equal to 30 but less than or equal to 61, if
found, it must add up the numbers in column B.

Something like
=SUMPRODUCT((A1:A100>=30)*(A1:A100<=61)*B1:B100)

 

[Mike]

Try =A1*AND(A1>=30,A1<=61)+B1 or =IF(AND(A1>=30,A1<=61),A1+B1,B1)

 

[Samantha]

Thanx for your help it works, great stuff. If you got the time coul
you please help me with my other problem....

Adding multiple columns horizontally and vertically. At present m
formula is as follows: =sumif(D6105,F124,E6:E105)
What I want to do is to include more colums vertically an
horizontally, the columns are as follows look i
(F6:F105,F124,G6:G105) and (H6:H105,F124,I6:I105) how do I incorporat
them into one formula.

Thanx

Samantha

 

[Frank Kabel]

Hi Samantha
one way )just simply add the sumif statements):
=sumif(D6105,F124,E6:E105)+sumif(F6:F105,F124,G6:G105)+sumif(H6:H105,
F124,I6:I105)

HTH
Frank

 

[Samantha]

Frank,

Thanx again for your help. I'm so sorry to make to pest of mysel
but...I need you expertise once again.

I need to calculate the following:

10% of G3, if the answer of G3 is greater than 1950, the answer of G
should remain at 1950, if it is less than 1950, it should show the 10
of G3.
in other words my answer should not exceed the value of 1950

Could you help, pleeeeze

Thanx

Samanth

 

[Frank Kabel]

Hi
try
=MIN(G5*0.1,1950)

Frank