VLOOKUP? SUMPRODUCT? not sure how to do this

  • Thread starter Thread starter Gambit-6
  • Start date Start date
G

Gambit-6

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
 
Maybe this

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

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


Mike
 
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
 
Hi,

'Just doesn't work' is about as unhelpful as it gets!
What does it do?
What do you expect it to do?
What have you ammended the formula too?
Where are you data?

Mike
 
Back
Top