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 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.

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
>
>

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
>
>

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
>
>

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.

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
>
>

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
>
>

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
>
>

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
>
>

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.

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
>
>

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
>
>

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
>
>