How to determine the value - 19 Jan?

[Eric]

Does anyone have any suggestions on how to determine the value?
There is a range between 4000 in cell C1 and 4400 in cell D1.
There is a value 4300 in cell A1 and value 800 in cell B1.
There is a value 3300 in cell A2 and value 700 in cell B2.
There is a value 5300 in cell A3 and value 900 in cell B3.
There is a value 4200 in cell A4 and value 600 in cell B4.

I would like to determine if the value under column A is >= 4000 in cell C1
and <4400 in cell D1, then sum all the value under column B.
In this case,
There is a value 4300 in cell A1 and value 800 in cell B1.
There is a value 4200 in cell A4 and value 600 in cell B4.
800 + 600 = 1400 should be returned in cell E1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

 

[Roger Govier]

Hi Eric

one way
=SUMPRODUCT(($A$1:$A$100>=$C1)*($A$1:$A$100<=$D1)*$B$1:$B$100)

 

[Max]

In E1: =SUMPRODUCT((A1:A4>=C1)*(A1:A4<D1),B1:B4)
where in C1: 4000, in D1: 4400
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik