First, what version of Excel are you using? If you're *not* using Excel 2007
then you can't use entire columns as range references with the SUMPRODUCT
function.

Try something like this:

=SUMPRODUCT(--(A1:A100="Apple"),--(B1:B100=C1),--(B1:B100<=C2))

Or, better yet, list the unique items in column D:

D1 = apple
D2 = orange
D3 = banana

Then, entered in E1 and drag copied down:

=SUMPRODUCT(--(A$1:A$100=D1),--(B$1:B$100=C$1),--(B$1:B$100<=C$2))

--
Biff
Microsoft Excel MVP


"gumgisen" wrote in message
...
The setup of the worksheet is as follows:
Col A Col B Col C Col D
Apple 12/09/2009 Start date No. of Apple within data period
Orange 08/09/2009 End date No. of Orange within data period
Apple 05/09/2009
Banana 21/07/2009
Apple 23/09/2009
etc.. etc...

Both column A and B retrieve data from a query linked to an Access
database
so the number of rows in those columns may change whenever I refresh the
query.

I have to count of the number of "fruit" occurance (Formula entered at D1
and D2) within a certain week which start date and end date are entered in
C1 & C2 respectively. I read some other posts in this forum and wrote the
forumla below:

=SUMPRODUCT(--(COUNTIF($B:$B,WEEKNUM($B:$B)=WEEKNUM($C$1)),--COUNTIF($A:$A,"Apple"))

And the result is #NUM!

Can someone please help? Thank you