formula to count how often something AND something occur.

Hi,

What i have is a table of data on one worksheet, the data consists of several columns, on another sheet i have a list of user IDs and corresponding names. What i want to do is count the number of times on the first worksheet a certain person appears in connection with a certain topic.

example of the raw data

1001 football
1002 rugby
1001 football
1003 football
1001 rugby
1001 football.

and the user table

1001 mr smith
1002 mrs jackson
1003 mr white

next to the user table i want to forexample count howmany times user ID 1001 is appears on the raw data table next to the word football.

I hope this makes sense to somebody and I appreciate any help given.

Cheers.

I tried both methods, eg

=SUMPRODUCT(--('January - NCC usage'!E2:E5000=A2)*--('January - NCC usage'!F2:F5000="xxxx"))

However in both cases it comes up with 0 if I use the cell reference, however if i use the number 1001, it will work. Unfortunately I really need it to work with the cell reference.

Is there anything else that i can do?

Thanks very much for the responses so far.

Hi,

Looks like you are mix and matching here - you formula contains components of both responses.

Is the cell reference on the same sheet? If not you will need to include the sheet name in the reference.

If you still get problems ZIP your file and post it here for us to look at?

Ed

Thank you so much for that, it was the format of the IDs all along!!