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.
Say the answers are in columns B thru E,
=COUNTIF(B2:E2,1)
HTH
--
AP
"AlexM" <[email protected]> a écrit dans le
message de news: [email protected]...
>
>
>
> 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.
>
>
> --
> AlexM
> ------------------------------------------------------------------------
> AlexM's Profile:
> http://www.excelforum.com/member.php...o&userid=34833
> View this thread: http://www.excelforum.com/showthread...hreadid=545829
>
One question to 0ne Consultant = only 1 answer (Either 1,2,3,4)..???
"AlexM" <[email protected]> wrote in
message news:[email protected]:
>
>
> 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.
>
>
> --
> AlexM
> ------------------------------------------------------------------------
> AlexM's Profile: http://www.excelforum.com/member.php...o&userid=34833
> View this thread: http://www.excelforum.com/showthread...hreadid=545829
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" <[email protected]> wrote in
message news:[email protected]...
>
>
>
> 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.
>
>
> --
> AlexM
> ------------------------------------------------------------------------
> AlexM's Profile:
> http://www.excelforum.com/member.php...o&userid=34833
> View this thread: http://www.excelforum.com/showthread...hreadid=545829
>
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.
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.
=SUMIF(A2:A5,"Karen",B2:B5)
HTH
--
AP
"AlexM" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> 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
> ------------------------------------------------------------------------
> AlexM's Profile:
> http://www.excelforum.com/member.php...o&userid=34833
> View this thread: http://www.excelforum.com/showthread...hreadid=545829
>
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?
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?
With your
CONSULTANT q1 q2 q3
Karen 4 3 3
Sam 4 2 3
Karen 4 4 4
Tricia 2 1 2
In Cells A1:D5
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
"AlexM" <[email protected]> wrote in
message news:[email protected]:
> 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
> ------------------------------------------------------------------------
> AlexM's Profile: http://www.excelforum.com/member.php...o&userid=34833
> View this thread: http://www.excelforum.com/showthread...hreadid=545829
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") !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks