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

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

I am using Excel 2003. Thank you for the quick respond. I tried your solution
and it works well.

As the number of data increases everyday and is part of other calculation in
my daily report at work, I would have to update the row number in the
SUMPRODUCT function from time to time which may contribute to an error if
there is a typo.

I tried to use COUNTA function to count no. of row used in col A (fruit) to
get the row number so I can use it in the SUMPRODUCT function but I failed to
combine it. By any chance it can be achieved instead of stating a very large
row number upon setup of the forumla?

Thank you and appreciate any help.


"T. Valko" wrote:

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

You can create dynamic ranges.

Let's assume the data in column A starts in cell A2. This will be the "key"
used to define dynamic ranges for both column A and column B. The data range
will *always* be a contiguous block, that is, there will never be empty
cells within the data range.

Goto the menu InsertNameDefine
Name: Fruit
Refers to:

=$A$2:INDEX($A$2:$A$65536,COUNTA($A$2:$A$65536)

Name: Dates
Refers to:

=$B$2:INDEX($B$2:$B$65536,COUNTA($A$2:$A$65536)

OK out

Then the formula becomes:

=SUMPRODUCT(--(Fruit=D1),--(Dates=C$1),--(Dates<=C$2))

--
Biff
Microsoft Excel MVP


"gumgisen" wrote in message
...
I am using Excel 2003. Thank you for the quick respond. I tried your
solution
and it works well.

As the number of data increases everyday and is part of other calculation
in
my daily report at work, I would have to update the row number in the
SUMPRODUCT function from time to time which may contribute to an error if
there is a typo.

I tried to use COUNTA function to count no. of row used in col A (fruit)
to
get the row number so I can use it in the SUMPRODUCT function but I failed
to
combine it. By any chance it can be achieved instead of stating a very
large
row number upon setup of the forumla?

Thank you and appreciate any help.


"T. Valko" wrote:

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

Thank you so much and it works perfectly.

Superb solution and very helpful. :)


"T. Valko" wrote:

You can create dynamic ranges.

Let's assume the data in column A starts in cell A2. This will be the "key"
used to define dynamic ranges for both column A and column B. The data range
will *always* be a contiguous block, that is, there will never be empty
cells within the data range.

Goto the menu InsertNameDefine
Name: Fruit
Refers to:

=$A$2:INDEX($A$2:$A$65536,COUNTA($A$2:$A$65536)

Name: Dates
Refers to:

=$B$2:INDEX($B$2:$B$65536,COUNTA($A$2:$A$65536)

OK out

Then the formula becomes:

=SUMPRODUCT(--(Fruit=D1),--(Dates=C$1),--(Dates<=C$2))

--
Biff
Microsoft Excel MVP


"gumgisen" wrote in message
...
I am using Excel 2003. Thank you for the quick respond. I tried your
solution
and it works well.

As the number of data increases everyday and is part of other calculation
in
my daily report at work, I would have to update the row number in the
SUMPRODUCT function from time to time which may contribute to an error if
there is a typo.

I tried to use COUNTA function to count no. of row used in col A (fruit)
to
get the row number so I can use it in the SUMPRODUCT function but I failed
to
combine it. By any chance it can be achieved instead of stating a very
large
row number upon setup of the forumla?

Thank you and appreciate any help.


"T. Valko" wrote:

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

Hi,

To make a range dynamic, simply select it and convert it to a list

In Excel 2003: Data Create List
In Excel 2007: Insert Table

One of the features of converting a range to a List is that it makes the
range auto expanding.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"gumgisen" wrote in message
...
I am using Excel 2003. Thank you for the quick respond. I tried your
solution
and it works well.

As the number of data increases everyday and is part of other calculation
in
my daily report at work, I would have to update the row number in the
SUMPRODUCT function from time to time which may contribute to an error if
there is a typo.

I tried to use COUNTA function to count no. of row used in col A (fruit)
to
get the row number so I can use it in the SUMPRODUCT function but I failed
to
combine it. By any chance it can be achieved instead of stating a very
large
row number upon setup of the forumla?

Thank you and appreciate any help.


"T. Valko" wrote:

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"gumgisen" wrote in message
...
Thank you so much and it works perfectly.

Superb solution and very helpful. :)


"T. Valko" wrote:

You can create dynamic ranges.

Let's assume the data in column A starts in cell A2. This will be the
"key"
used to define dynamic ranges for both column A and column B. The data
range
will *always* be a contiguous block, that is, there will never be empty
cells within the data range.

Goto the menu InsertNameDefine
Name: Fruit
Refers to:

=$A$2:INDEX($A$2:$A$65536,COUNTA($A$2:$A$65536)

Name: Dates
Refers to:

=$B$2:INDEX($B$2:$B$65536,COUNTA($A$2:$A$65536)

OK out

Then the formula becomes:

=SUMPRODUCT(--(Fruit=D1),--(Dates=C$1),--(Dates<=C$2))

--
Biff
Microsoft Excel MVP


"gumgisen" wrote in message
...
I am using Excel 2003. Thank you for the quick respond. I tried your
solution
and it works well.

As the number of data increases everyday and is part of other
calculation
in
my daily report at work, I would have to update the row number in the
SUMPRODUCT function from time to time which may contribute to an error
if
there is a typo.

I tried to use COUNTA function to count no. of row used in col A
(fruit)
to
get the row number so I can use it in the SUMPRODUCT function but I
failed
to
combine it. By any chance it can be achieved instead of stating a very
large
row number upon setup of the forumla?

Thank you and appreciate any help.


"T. Valko" wrote:

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

Thank you for the suggestion. :)

I tried to create a list but failed as my data range is connected to
external data range (query in Access database) and I have to keep the
connection to retreive new data upon refresh and update. Any thoughts?


"Ashish Mathur" wrote:

Hi,

To make a range dynamic, simply select it and convert it to a list

In Excel 2003: Data Create List
In Excel 2007: Insert Table

One of the features of converting a range to a List is that it makes the
range auto expanding.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"gumgisen" wrote in message
...
I am using Excel 2003. Thank you for the quick respond. I tried your
solution
and it works well.

As the number of data increases everyday and is part of other calculation
in
my daily report at work, I would have to update the row number in the
SUMPRODUCT function from time to time which may contribute to an error if
there is a typo.

I tried to use COUNTA function to count no. of row used in col A (fruit)
to
get the row number so I can use it in the SUMPRODUCT function but I failed
to
combine it. By any chance it can be achieved instead of stating a very
large
row number upon setup of the forumla?

Thank you and appreciate any help.


"T. Valko" wrote:

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