| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking up data
 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Looking up data
One question to 0ne Consultant = only 1 answer (Either 1,2,3,4)..???
"AlexM" wrote in message : 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Looking up data
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Looking up data
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Looking up data
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Looking up data
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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") !! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
| Importing Data | Excel Worksheet Functions | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
| Line Graph Data Recognition | Charts and Charting in Excel | |||
Hybrid Mode
