A follow up Question

[Andrew Mackenzie]

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

 

[Mike H]

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]

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

..

 

[Andrew Mackenzie]

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

 

[Mike H]

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