Hi

Can anyone advise which excel function should I use to return the desired
results? i.e. to count the no. of times e.g. computer components appear in
the month of Apr?

I am using a interactive worksheet i.e. if I changes the month to e.g. Jun
in worksheet 2, the count should also changes.

I tried countif and counta but it is not the desired result I am looking at
cos when the month changes in worksheet 2, the count is not updated.

Example
Worksheet 1
Product cat Product Apr May
Jun......
Computer components Mouse 5 10
Computer software Windows 97 4
Computer components Keyboard 5

Worksheet 2
Product cat Apr
Computer components 2
Computer software 1

Thanks

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=Sheet2!$A2),--(Sheet1!C$2:C$20<""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Carol" wrote in message
...
Hi

Can anyone advise which excel function should I use to return the desired
results? i.e. to count the no. of times e.g. computer components appear in
the month of Apr?

I am using a interactive worksheet i.e. if I changes the month to e.g. Jun
in worksheet 2, the count should also changes.

I tried countif and counta but it is not the desired result I am looking
at
cos when the month changes in worksheet 2, the count is not updated.

Example
Worksheet 1
Product cat Product Apr May
Jun......
Computer components Mouse 5 10
Computer software Windows 97 4
Computer components Keyboard 5

Worksheet 2
Product cat Apr
Computer components 2
Computer software 1

Thanks

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=Sheet2!$A2),--(Sheet1!C$2:C$20<""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Carol" wrote in message
...
Hi

Can anyone advise which excel function should I use to return the desired
results? i.e. to count the no. of times e.g. computer components appear in
the month of Apr?

I am using a interactive worksheet i.e. if I changes the month to e.g. Jun
in worksheet 2, the count should also changes.

I tried countif and counta but it is not the desired result I am looking
at
cos when the month changes in worksheet 2, the count is not updated.

Example
Worksheet 1
Product cat Product Apr May
Jun......
Computer components Mouse 5 10
Computer software Windows 97 4
Computer components Keyboard 5

Worksheet 2
Product cat Apr
Computer components 2
Computer software 1

Thanks

Hi Bob

Thanks for the quick reponse.

I tried your formula below but error message returned "#VALUE!"

And system auto correct the error to
=SUMPRODUCT(Sheet1!$A$2:$A$20=Sheet2!$A2)*(Sheet1! C$2:C$20<"") but still
incorrect.

May I know where could be the possible error?

Thanks
Carol

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=Sheet2!$A2),--(Sheet1!C$2:C$20<""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Carol" wrote in message
...
Hi

Can anyone advise which excel function should I use to return the desired
results? i.e. to count the no. of times e.g. computer components appear in
the month of Apr?

I am using a interactive worksheet i.e. if I changes the month to e.g. Jun
in worksheet 2, the count should also changes.

I tried countif and counta but it is not the desired result I am looking
at
cos when the month changes in worksheet 2, the count is not updated.

Example
Worksheet 1
Product cat Product Apr May
Jun......
Computer components Mouse 5 10
Computer software Windows 97 4
Computer components Keyboard 5

Worksheet 2
Product cat Apr
Computer components 2
Computer software 1

Thanks

Your first clue should have been the autocorrecting. Enter the formula as
Bob posted (the easiest way is to copy and paste it, rather than typing it
in). Also, you will need to adjust the ranges in the formula to suit your
situation.

Regards,
Fred.

"Carol" wrote in message
...
Hi Bob

Thanks for the quick reponse.

I tried your formula below but error message returned "#VALUE!"

And system auto correct the error to
=SUMPRODUCT(Sheet1!$A$2:$A$20=Sheet2!$A2)*(Sheet1! C$2:C$20<"") but still
incorrect.

May I know where could be the possible error?

Thanks
Carol

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=Sheet2!$A2),--(Sheet1!C$2:C$20<""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Carol" wrote in message
...
Hi

Can anyone advise which excel function should I use to return the
desired
results? i.e. to count the no. of times e.g. computer components appear
in
the month of Apr?

I am using a interactive worksheet i.e. if I changes the month to e.g.
Jun
in worksheet 2, the count should also changes.

I tried countif and counta but it is not the desired result I am
looking
at
cos when the month changes in worksheet 2, the count is not updated.

Example
Worksheet 1
Product cat Product Apr May
Jun......
Computer components Mouse 5 10
Computer software Windows 97 4
Computer components Keyboard 5

Worksheet 2
Product cat Apr
Computer components 2
Computer software 1

Thanks