|
Looking up data
:confused: 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 |
Looking up data
One question to 0ne Consultant = only 1 answer (Either 1,2,3,4)..???
"AlexM" wrote in message : :confused: 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 |
Looking up data
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" wrote in message ... :confused: 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 |
Looking up data
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 |
Looking up data
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 |
Looking up data
=SUMIF(A2:A5,"Karen",B2:B5)
HTH -- AP "AlexM" a écrit dans le message de news: ... 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 |
Looking up data
:confused: 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 ------------------------------------------------------------------------ AlexM's Profile: http://www.excelforum.com/member.php...o&userid=34833 View this thread: http://www.excelforum.com/showthread...hreadid=545829 |
Looking up data
:confused: 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 ------------------------------------------------------------------------ AlexM's Profile: http://www.excelforum.com/member.php...o&userid=34833 View this thread: http://www.excelforum.com/showthread...hreadid=545829 |
Looking up data
:confused: 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 ------------------------------------------------------------------------ AlexM's Profile: http://www.excelforum.com/member.php...o&userid=34833 View this thread: http://www.excelforum.com/showthread...hreadid=545829 |
Looking up data
:confused: 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 ------------------------------------------------------------------------ AlexM's Profile: http://www.excelforum.com/member.php...o&userid=34833 View this thread: http://www.excelforum.com/showthread...hreadid=545829 |
Looking up data
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" wrote in message : 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 |
Looking up data
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") !! |
| All times are GMT +1. The time now is 03:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com