How to determine the value - 19 Jan?
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 |
How to determine the value - 19 Jan?
Hi Eric
one way =SUMPRODUCT(($A$1:$A$100=$C1)*($A$1:$A$100<=$D1)* $B$1:$B$100) -- Regards Roger Govier "Eric" wrote in message ... 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 |
How to determine the value - 19 Jan?
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 --- "Eric" wrote: 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 |
All times are GMT +1. The time now is 03:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com