Hello again Mike, this is getting a bit embarrassing, when i amend it in my
worksheet, the first cell works perfectly, however when i amend the column
range for subsequent cells it doesn't!
everything matches just doesn't work and yet again i'm scratching my head

"Mike H" wrote:

Maybe this

=SUMPRODUCT((A2:A5=A1)*(B2:D5=2))

Where your lookup value is in A1 and your data are in A2 - D5


Mike

"Gambit-6" wrote:

Hi all
Hope you can help with what will probably turn out to be a really simple
problem

In my worksheet, i need to be able to run a 'report' so when i enter a
criteria it will only access information held within all that persons result
e.g.
Column 1 Column 2 Column 3 Column 4
Bloggs, Joe 2 4 2
Bloggs, Joe 2 0 0
Smith, John 1 4 4
Bloggs, Joe 1 4 4

Basically i need to find out how many times Joe Bloggs returns a "2" result,
and a "4" etc. Each of these returns would be in its own colomn. However
rather than have a seperate worksheet for each individual i would like to
enter Bloggs, Joe into Cell A1 and get the results below. It's a fairly big
sheet covering 500 rows, 50 odd columns and about 200 staff. So one sheet
that i can quickly access the information would be ideal