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

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.

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

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