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

"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

Mike you are a Steely Eyed Excel Man
Can't believe it was that simple
Cheers

"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

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

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

"Gambit-6" wrote:

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