sumproduct returns #value!

[spig]

I have the following formula:

=SUMPRODUCT(--(C$1:CW$1-DAY(C$1:CW$1)+1=$DD$9),C10:CW10)

If it reads from an empty cell, it works fine but it is reading from a cell which itself contains an if formula and therefore returns #VALUE! which i don't want. I want it to return a 0 or the actual number but not a #VALUE!

How do I alter this formula to avoid #VALUE! being returned?

Can anyone help me please - I can copy spreadsheet details if necessary.

Thanks

 

[T. Valko]

Hi,

I changed all formulas to read 0 instead of "" but it now shows dates as 00-Jan-00 - how do I get rid of that so cells are clear? In excel 2003, you could go to tools and options and remove zero's. I am working on excel 2007. However, it did resolve the #VALUE! issue so thanks for that

Regards

David

To suppress the display of 0 values in Excel 2007...

Office button
Excel Options
Advanced
Scroll down to the section: Display Options For This Worksheet
Uncheck: Show a zero in cells that have a zero value
OK out

 

[spig]

Ok, try it like this:

=SUMPRODUCT(--(TEXT(C$1:CW$1,"mmmyyyy")=TEXT($DD$9,"mmmyyyy")),C10:CW10)

Great - this seems to have done the trick. Many, many thanks to you all - much appreciated for your skills

 

[Aladin Akyurek]

Great - this seems to have done the trick. Many, many thanks to you all - much appreciated for your skills

Did you try the other suggestion then?

 

[T. Valko]

Great - this seems to have done the trick. Many, many thanks to you all - much appreciated for your skills

Good deal. Thanks for the feedback!