Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF limitations, alternatives?
I'm entering the results of a survey into Excel. The answer to one of the
questions was a number from 1-6. I entered all the data, then used COUNTIF to tell me how many respondents entered 1, how many 2, how many 3, etc. Now I'm getting the next survey coming back, almost the same results, but respondents are allowed to tick multiple boxes. So someone may enter 1 and 4 and 6 to the same question! Can I enter something like 1,4,6 in the same column and still get a breakdown of how many respondents ticked each number? I don't think COUNTIF can handle this. TIA Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF limitations, alternatives?
use multiple columns and sum multiple COUNTIFs
-- Kind regards, Niek Otten Microsoft MVP - Excel "Steve" wrote in message ... | I'm entering the results of a survey into Excel. The answer to one of the | questions was a number from 1-6. I entered all the data, then used COUNTIF | to tell me how many respondents entered 1, how many 2, how many 3, etc. | | Now I'm getting the next survey coming back, almost the same results, but | respondents are allowed to tick multiple boxes. So someone may enter 1 and 4 | and 6 to the same question! | | Can I enter something like 1,4,6 in the same column and still get a | breakdown of how many respondents ticked each number? I don't think COUNTIF | can handle this. | | TIA | | Steve | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF limitations, alternatives?
=SUMPRODUCT((COUNTIF(A:A,{1,4,6})))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Steve" wrote in message ... I'm entering the results of a survey into Excel. The answer to one of the questions was a number from 1-6. I entered all the data, then used COUNTIF to tell me how many respondents entered 1, how many 2, how many 3, etc. Now I'm getting the next survey coming back, almost the same results, but respondents are allowed to tick multiple boxes. So someone may enter 1 and 4 and 6 to the same question! Can I enter something like 1,4,6 in the same column and still get a breakdown of how many respondents ticked each number? I don't think COUNTIF can handle this. TIA Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF limitations, alternatives?
Bob, you obviously can't substitute in for the 1,4,6 Cells F1, F2,
F3(containing the 1,4 and 6). "Bob Phillips" wrote: =SUMPRODUCT((COUNTIF(A:A,{1,4,6}))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Steve" wrote in message ... I'm entering the results of a survey into Excel. The answer to one of the questions was a number from 1-6. I entered all the data, then used COUNTIF to tell me how many respondents entered 1, how many 2, how many 3, etc. Now I'm getting the next survey coming back, almost the same results, but respondents are allowed to tick multiple boxes. So someone may enter 1 and 4 and 6 to the same question! Can I enter something like 1,4,6 in the same column and still get a breakdown of how many respondents ticked each number? I don't think COUNTIF can handle this. TIA Steve |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF limitations, alternatives?
Yeah you can
=SUMPRODUCT((COUNTIF(A:A,F1:F3))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim May" wrote in message ... Bob, you obviously can't substitute in for the 1,4,6 Cells F1, F2, F3(containing the 1,4 and 6). "Bob Phillips" wrote: =SUMPRODUCT((COUNTIF(A:A,{1,4,6}))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Steve" wrote in message ... I'm entering the results of a survey into Excel. The answer to one of the questions was a number from 1-6. I entered all the data, then used COUNTIF to tell me how many respondents entered 1, how many 2, how many 3, etc. Now I'm getting the next survey coming back, almost the same results, but respondents are allowed to tick multiple boxes. So someone may enter 1 and 4 and 6 to the same question! Can I enter something like 1,4,6 in the same column and still get a breakdown of how many respondents ticked each number? I don't think COUNTIF can handle this. TIA Steve |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF limitations, alternatives?
Steve -
Regarding alternatives: If you want to use a pivot table to summarize results, you should change your "database" to have six columns of tick vs. no tick for that question's responses instead of having only one column for the original mutually exclusive response. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Steve" wrote in message ... I'm entering the results of a survey into Excel. The answer to one of the questions was a number from 1-6. I entered all the data, then used COUNTIF to tell me how many respondents entered 1, how many 2, how many 3, etc. Now I'm getting the next survey coming back, almost the same results, but respondents are allowed to tick multiple boxes. So someone may enter 1 and 4 and 6 to the same question! Can I enter something like 1,4,6 in the same column and still get a breakdown of how many respondents ticked each number? I don't think COUNTIF can handle this. TIA Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternatives to GET.CELL and VB? | Excel Worksheet Functions | |||
What are the alternatives ??? | Excel Discussion (Misc queries) | |||
Alternatives to Excel | Excel Discussion (Misc queries) | |||
Pocket Excel Alternatives? | Excel Discussion (Misc queries) |