SUMPRODUCT Headache

[TFCJamieFay]

Hi All,

I'm having problems with a formula that, until this morning, has worked for months! I have used the error checking tools in Excel, but to no avail. Can anyone help please?

This is my formula (the value of A6 is "01/03/2009" which is in the same format as Sheet1, column A)...

=SUMPRODUCT(--('[Daily Tracking 2009.xls]Sheet1'!$A$2:$A$10000-DAY('[Daily Tracking 2009.xls]Sheet1'!$A$2:$A$10000)+1=$A$6),--('[Daily Tracking 2009.xls]Sheet1'!$I$2:$I$10000="Accept"))

...However, when I remove the part that looks for "Accept" it works...

=SUMPRODUCT(--('[Daily Tracking 2009.xls]Sheet1'!$A$2:$A$10000-DAY('[Daily Tracking 2009.xls]Sheet1'!$A$2:$A$10000)+1=$A$6))

...I don't understand!

Any help would be greatly appriciated!

Jay

 

[RoryA]

Have you tried:

=SUMPRODUCT(--(('[Daily Tracking 2009.xls]Sheet1'!$A$2:$A$10000-DAY('[Daily Tracking 2009.xls]Sheet1'!$A$2:$A$10000)+1)=$A$6),--('[Daily Tracking 2009.xls]Sheet1'!$I$2:$I$10000="Accept"))

 

[TFCJamieFay]

Thanks Rorya,

I've just tried your sugestion but I get an "Invalid reference" message box pop up when I exit the cell.

Jay

 

[RoryA]

I didn't change any references so I don't see how that could happen!

 

[TFCJamieFay]

Sorry, that was my fault. I missed a typo! It still gives me a "#N/A" error though.

Thanks again for your help!

Jay

 

[RoryA]

I would check your source data for error values then

 

[TFCJamieFay]

Cheers, that was exactly it. How did I miss that?!