Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT help
Hi, I have three columns. One containing a "value" taken from a list, another an amount and the third column contains a date. What I need to do is for a particular value, find out the sum of all the amounts for a particular month. This is the formula I have written but the sum is not being calculated for the particular date range. Column D Column E Column F 15 March, 2005 23344 UK Perdiem 17 March, 2005 739874 UK Perdiem =SUMPRODUCT((F5:F150)*(I5:I150="UK Perdiem")*(D5:D150= "15 March, 2005" & D5:D150<= "31 March, 2005")) The value shows 0 but if I remove the date calculation it works fine. Can anybody please help me with the formula? Thanks in advance -- svvm ------------------------------------------------------------------------ svvm's Profile: http://www.excelforum.com/member.php...o&userid=21501 View this thread: http://www.excelforum.com/showthread...hreadid=380469 |
#2
|
|||
|
|||
svvm wrote:
Hi, I have three columns. One containing a "value" taken from a list, another an amount and the third column contains a date. What I need to do is for a particular value, find out the sum of all the amounts for a particular month. This is the formula I have written but the sum is not being calculated for the particular date range. Column D Column E Column F 15 March, 2005 23344 UK Perdiem 17 March, 2005 739874 UK Perdiem =SUMPRODUCT((F5:F150)*(I5:I150="UK Perdiem")*(D5:D150= "15 March, 2005" & D5:D150<= "31 March, 2005")) The value shows 0 but if I remove the date calculation it works fine. Can anybody please help me with the formula? Thanks in advance =SUMPRODUCT($F$5:$F$150,($I$5:$I$150="UK Perdiem")+0,($D$5:$D$150= "15-Mar-05"+0)+0, ($D$5:$D$150<= "31-Mar-05"+0)+0) -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#3
|
|||
|
|||
The problem is probably that your date value is not being recognized.
try =SUMPRODUCT((F5:F150)*(I5:I150="UK Perdiem")*(D5:D150= datevalue("15 March, 2005") & D5:D150<= datevalue(("31 March, 2005")) Note with my verison of Excel I had to use "March 15, 2005" You may have to paly a bit with format. "svvm" wrote: Hi, I have three columns. One containing a "value" taken from a list, another an amount and the third column contains a date. What I need to do is for a particular value, find out the sum of all the amounts for a particular month. This is the formula I have written but the sum is not being calculated for the particular date range. Column D Column E Column F 15 March, 2005 23344 UK Perdiem 17 March, 2005 739874 UK Perdiem =SUMPRODUCT((F5:F150)*(I5:I150="UK Perdiem")*(D5:D150= "15 March, 2005" & D5:D150<= "31 March, 2005")) The value shows 0 but if I remove the date calculation it works fine. Can anybody please help me with the formula? Thanks in advance -- svvm ------------------------------------------------------------------------ svvm's Profile: http://www.excelforum.com/member.php...o&userid=21501 View this thread: http://www.excelforum.com/showthread...hreadid=380469 |
#4
|
|||
|
|||
One simple way round the problem of date recognition is to put your start and end dates in cells and reference the cells in the formula. This has the added benefit of making it very quick and easy to calculate over any chosen date period. -- Alex Delamain ------------------------------------------------------------------------ Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273 View this thread: http://www.excelforum.com/showthread...hreadid=380469 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |