Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to determine the max. value? | Excel Worksheet Functions | |||
How to determine the value? | Excel Worksheet Functions | |||
How to determine the value? | Excel Worksheet Functions | |||
How to Determine 1st, 2nd & 3rd for a PWD | Excel Discussion (Misc queries) |