Looking up data

[AlexM]

Baically, I have a worksheet with names of consultants in the first
column, and then question numbers at the top of the following columns.
Each question could have an answer of 1 - 4, grading the responses
given for service. 1 is excellent.

How do I find out how many 'excellents' a particular consultant has
received for a particular question? I have looked at Vlookup,
Getpivotdata and others, with no joy.

 

[Ardus Petus]

Say the answers are in columns B thru E,
=COUNTIF(B2:E2,1)

HTH

 

[JimMay]

One question to 0ne Consultant = only 1 answer (Either 1,2,3,4)..???

 

[Daniel CHEN]

Use function countif, like:
=COUNTIF(B2:Z2,1)
to get total number of "excellent"(1) for consultant in row 2.


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================

 

[AlexM]

The countif is picking up the total number of each 1 or excellent, but
not distinguishing between who got them.

Think I didn't explain well, sorry -

I need to somehow find a formula that will look up the consultant name,
then look up the quality response that particular person got for one of
3 questions, and then tell me how many of each response they received.
My table looks a bit like this:


CONSULTANT q1 q2 q3
Karen 4 3 3
Sam 4 2 3
Karen 4 4 4
Tricia 2 1 2


And I would need to know that Karen got two '4' responses to question
1.
I'm stumped.

 

[AlexM]

The countif is picking up the total number of each 1 or excellent, but
not distinguishing between who got them.

Think I didn't explain well, sorry -

I need to somehow find a formula that will look up the consultant name,
then look up the quality response that particular person got for one of
3 questions, and then tell me how many of each response they received.
My table looks a bit like this:


CONSULTANT q1 q2 q3
Karen 4 3 3
Sam 4 2 3
Karen 4 4 4
Tricia 2 1 2


And I would need to know that Karen got two '4' responses to question
1.
I'm stumped.

 

[Ardus Petus]

=SUMIF(A2:A5,"Karen",B2:B5)

HTH

 

[AlexM]

That gives me the total sum of Karens responses for qu 1 (ie 8),. but
not the number of times she got a response of 4. I was expecting the
answer to be 2, instead of the total given: 8.

Is there a way I can just count the number of times the 4 reponse was
given, rather than adding the figures together?

 

[AlexM]

That gives me the total sum of Karens responses for qu 1 (ie 8),. but
not the number of times she got a response of 4. I was expecting the
answer to be 2, instead of the total given: 8.

Is there a way I can just count the number of times the 4 reponse was
given, rather than adding the figures together?

 

[AlexM]

That gives me the total sum of Karens responses for qu 1 (ie 8),. but
not the number of times she got a response of 4. I was expecting the
answer to be 2, instead of the total given: 8.

Is there a way I can just count the number of times the 4 reponse was
given, rather than adding the figures together?

 

[AlexM]

That gives me the total sum of Karens responses for qu 1 (ie 8),. but
not the number of times she got a response of 4. I was expecting the
answer to be 2, instead of the total given: 8.

Is there a way I can just count the number of times the 4 reponse was
given, rather than adding the figures together?

 

[JimMay]

With your

CONSULTANT q1 q2 q3
Karen 4 3 3
Sam 4 2 3
Karen 4 4 4
Tricia 2 1 2

In Cells A15

I entered in Cell A9 - Karen-4 (of course the "-4" represent the
score 4
Then in cell B9 I entered:
=SUMPRODUCT(--($A$2:$A$5=LEFT($A9,LEN($A9)-2)),--(B$2:B$5=VALUE(RIGHT($A9,1))))

Then I copied B9 across to D9;

Hope this helps.
Jim May

 

[Guest]

CONSULTANT q1 q2 q3
Karen 4 3 3
Sam 4 2 3
Karen 4 4 4
Tricia 2 1 2

Insert a column after q3, call it con_q1.
Key in the formula a2&text(b2,"00") in cell e2.
repeat for rows 3 to 5.
Then use countif(e2:e5,"Karen04"), this will give you 2.
Disadvantage: to count different quality of responses, say 1,2,3 & 4,
you'd have to use 4 cells of countif(e2:e5,"Karen01"),
countif(e2:e5,"Karen02"),countif(e2:e5,"Karen03"),countif(e2:e5,"Karen04") !!