SUMPRODUCT Headache

TFCJamieFay

Active Member
Joined
Oct 3, 2007
Messages
480
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)...
Code:
=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...
Code:
=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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Have you tried:
Code:
=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"))
 
Upvote 0
Thanks Rorya,

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

Jay
 
Upvote 0
I didn't change any references so I don't see how that could happen!
 
Upvote 0
Sorry, that was my fault. I missed a typo! It still gives me a "#N/A" error though.

Thanks again for your help!

Jay
 
Upvote 0
I would check your source data for error values then
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top