I have now tried to incorporate the "BOMONTH" formula into the larger
formula below but I am getting #VALUE.

=SUMPRODUCT((dciValueDate<=+$L$64)*(dciValueDate= (EOMONTH($L$64,-1)+1))*(dc
iGBPAmount))

The formula is attempting to sum the amounts in the range dciGBPAmount where
the date in the column dciValueDate is in March 2008. L64 contains the date
31st March 2008.


Any ideas what is wrong with the syntax?

Cheers,

Andrew

Hi,

With the exception of the unnecessary + sign the formula is fine and should
read.

=SUMPRODUCT((dciValueDate<=$L$64)*(dciValueDate=( EOMONTH($L$64,-1)+1))*(dciGBPAmount))

If yoyur getting a Value error then either L64 or the range you are summing
aren't a date and or numbers.

Try this
=isnumber(L64) should evaluate as true and likewise for the sumrange.

Mike

"Andrew Mackenzie" wrote:

I have now tried to incorporate the "BOMONTH" formula into the larger
formula below but I am getting #VALUE.

=SUMPRODUCT((dciValueDate<=+$L$64)*(dciValueDate= (EOMONTH($L$64,-1)+1))*(dc
iGBPAmount))

The formula is attempting to sum the amounts in the range dciGBPAmount where
the date in the column dciValueDate is in March 2008. L64 contains the date
31st March 2008.


Any ideas what is wrong with the syntax?

Cheers,

Andrew

Thanks Mike,

But I have done as you suggested with ISNUMBER and all are true. I have
also double checked that the range names are correct and, for good order,
removed the unnecessary + sign.

Any other ideas please - this is beginning to drive me nuts!

Cheers
Andrew

..
"Mike H" wrote in message
...
Hi,

With the exception of the unnecessary + sign the formula is fine and
should
read.


=SUMPRODUCT((dciValueDate<=$L$64)*(dciValueDate=( EOMONTH($L$64,-1)+1))*(dci
GBPAmount))

If yoyur getting a Value error then either L64 or the range you are
summing
aren't a date and or numbers.

Try this
=isnumber(L64) should evaluate as true and likewise for the sumrange.

Mike

"Andrew Mackenzie" wrote:

I have now tried to incorporate the "BOMONTH" formula into the larger
formula below but I am getting #VALUE.


=SUMPRODUCT((dciValueDate<=+$L$64)*(dciValueDate= (EOMONTH($L$64,-1)+1))*(dc
iGBPAmount))

The formula is attempting to sum the amounts in the range dciGBPAmount
where
the date in the column dciValueDate is in March 2008. L64 contains the
date
31st March 2008.


Any ideas what is wrong with the syntax?

Cheers,

Andrew

OK,

Figured it myself in the end. If anyone's interested, my range names
included the column title - so Mike was spot on.

Cheers,
Andrew

"Andrew Mackenzie" wrote in message
...
Thanks Mike,

But I have done as you suggested with ISNUMBER and all are true. I have
also double checked that the range names are correct and, for good order,
removed the unnecessary + sign.

Any other ideas please - this is beginning to drive me nuts!

Cheers
Andrew

.
"Mike H" wrote in message
...
Hi,

With the exception of the unnecessary + sign the formula is fine and
should
read.



=SUMPRODUCT((dciValueDate<=$L$64)*(dciValueDate=( EOMONTH($L$64,-1)+1))*(dci
GBPAmount))

If yoyur getting a Value error then either L64 or the range you are
summing
aren't a date and or numbers.

Try this
=isnumber(L64) should evaluate as true and likewise for the sumrange.

Mike

"Andrew Mackenzie" wrote:

I have now tried to incorporate the "BOMONTH" formula into the larger
formula below but I am getting #VALUE.



=SUMPRODUCT((dciValueDate<=+$L$64)*(dciValueDate= (EOMONTH($L$64,-1)+1))*(dc
iGBPAmount))

The formula is attempting to sum the amounts in the range dciGBPAmount
where
the date in the column dciValueDate is in March 2008. L64 contains
the
date
31st March 2008.


Any ideas what is wrong with the syntax?

Cheers,

Andrew

Hi,

There's nothing else I'm aware of that would give a value error. Just in
case I never made it completely clear, any text in the sum range even if it's
not actually being summed by the formula will cause a value error so make
sure there are no text values anywhere in dciGBPAmount. Also ranges in
sumproduct cannot be full columns but this would give a NUM error.



Mike


"Andrew Mackenzie" wrote:

Thanks Mike,

But I have done as you suggested with ISNUMBER and all are true. I have
also double checked that the range names are correct and, for good order,
removed the unnecessary + sign.

Any other ideas please - this is beginning to drive me nuts!

Cheers
Andrew

..
"Mike H" wrote in message
...
Hi,

With the exception of the unnecessary + sign the formula is fine and
should
read.


=SUMPRODUCT((dciValueDate<=$L$64)*(dciValueDate=( EOMONTH($L$64,-1)+1))*(dci
GBPAmount))

If yoyur getting a Value error then either L64 or the range you are
summing
aren't a date and or numbers.

Try this
=isnumber(L64) should evaluate as true and likewise for the sumrange.

Mike

"Andrew Mackenzie" wrote:

I have now tried to incorporate the "BOMONTH" formula into the larger
formula below but I am getting #VALUE.


=SUMPRODUCT((dciValueDate<=+$L$64)*(dciValueDate= (EOMONTH($L$64,-1)+1))*(dc
iGBPAmount))

The formula is attempting to sum the amounts in the range dciGBPAmount
where
the date in the column dciValueDate is in March 2008. L64 contains the
date
31st March 2008.


Any ideas what is wrong with the syntax?

Cheers,

Andrew