adding using if 1

[jupops]

Hi can anybody help.

I am trying to write a formula that will add up a total in column B if the criteria is met in Column A, in this case if it falls between two sets of numbers (1 to 10), the formula I tried (but failed) is

=IF((A1:A10>0)*( A1:A10<10),"sumproduct+B1:B10",0)

an example is

5 10
6 45
45 6
95 900

so I would get the answer 55

Thank you

Jupops

 

[TonyJollans]

Hi Jupops,

You're on the right lines with SUMPRODUCT. This should do it:

[blue][tt]=SUMPRODUCT((A1:A4>1)*(A1:A4<10)*B1:B4)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[jupops]

Thank You, this is great