What function to use to return the no. of same value by mths

[Carol]

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]

=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]

=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]

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

 

[Fred Smith]

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.