Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!! Counting Selective Responses if 2 conditions are met?
Dear ALL,
I have an excel sheet similar to below Sex Age Range Employment Level Work Location Length of Service Management operates an open door policy when it comes to safety. 2 4 1 2 1 1 3 1 5 2 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 2 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 5 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 5 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 The first 5 questions are demographic questions. The last is an "opinion" question. If somebody can help me with the syntax for getting one of these right I shall be eternally grateful!!!! WHAT I NEED TO DO: I need to be able to count the number of "1's" in the column "Management etc" dependant on the response in a demographic column eg "sex". Eg If the response is "1" in sex (meaning male) and '5" in management (meaning strongly agree); then count the number of "5's" only. The result in the target cell returned to me only delivers the males that strongly agreed. The correct answer to this example should be 6. Once I have this syntax I am thinking I just then modify it for all the other demographic questions. MANY THANKS. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!! Counting Selective Responses if 2 conditions are met?
David,
Do yourself a favor, and learn how to use Pivot Tables. You will be able to answer all your combinations of questions without using a single formula. Select your table, choose Data / Pivot Table... and click through to the end. Then drag Sex, Age, Range Employment Level, Work Location, Length of Service to the Row Fields area, and drag "Management operates an open door policy when it comes to safety." to the columns field and then again to the data field. And then you can hide categories or subcategories to get a score count of any combination of variables. HTH, Bernie MS Excel MVP "David G Broadbent" wrote in message u... Dear ALL, I have an excel sheet similar to below Sex Age Range Employment Level Work Location Length of Service Management operates an open door policy when it comes to safety. 2 4 1 2 1 1 3 1 5 2 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 2 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 5 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 5 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 The first 5 questions are demographic questions. The last is an "opinion" question. If somebody can help me with the syntax for getting one of these right I shall be eternally grateful!!!! WHAT I NEED TO DO: I need to be able to count the number of "1's" in the column "Management etc" dependant on the response in a demographic column eg "sex". Eg If the response is "1" in sex (meaning male) and '5" in management (meaning strongly agree); then count the number of "5's" only. The result in the target cell returned to me only delivers the males that strongly agreed. The correct answer to this example should be 6. Once I have this syntax I am thinking I just then modify it for all the other demographic questions. MANY THANKS. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!! Counting Selective Responses if 2 conditions are met?
I had looked at this as well.
Maybe I am not understanding something. Am creating a spreadsheet that will separate and analyse data from the first worksheet which is an import from a survey program I use. I then have other worksheets for the demographic questions which shall instantly do all the "work". They also draw the charts I want. this is then linked to a Word report format directly. I have achieved all of this for the survey data as a whole. Once I have the syntax for harvesting the demographics and then populate the necessary cells it should all be automatic (with no input from me) once I import the data sheet into the first worksheet. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... David, Do yourself a favor, and learn how to use Pivot Tables. You will be able to answer all your combinations of questions without using a single formula. Select your table, choose Data / Pivot Table... and click through to the end. Then drag Sex, Age, Range Employment Level, Work Location, Length of Service to the Row Fields area, and drag "Management operates an open door policy when it comes to safety." to the columns field and then again to the data field. And then you can hide categories or subcategories to get a score count of any combination of variables. HTH, Bernie MS Excel MVP "David G Broadbent" wrote in message u... Dear ALL, I have an excel sheet similar to below Sex Age Range Employment Level Work Location Length of Service Management operates an open door policy when it comes to safety. 2 4 1 2 1 1 3 1 5 2 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 2 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 5 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 5 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 The first 5 questions are demographic questions. The last is an "opinion" question. If somebody can help me with the syntax for getting one of these right I shall be eternally grateful!!!! WHAT I NEED TO DO: I need to be able to count the number of "1's" in the column "Management etc" dependant on the response in a demographic column eg "sex". Eg If the response is "1" in sex (meaning male) and '5" in management (meaning strongly agree); then count the number of "5's" only. The result in the target cell returned to me only delivers the males that strongly agreed. The correct answer to this example should be 6. Once I have this syntax I am thinking I just then modify it for all the other demographic questions. MANY THANKS. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!! Counting Selective Responses if 2 conditions are met?
Pivot tables can be automated as well, and the data extracted.
But, if you want to go through with formulas, then take a look at SUMPRODUCT =SUMPRODUCT((A1:A1000=1)*(E1:E1000=5)) will return the count of 5's in column E where column A is 1. Of course, the ranges can be on a separate sheet. The range size and orientation must be the same. HTH, Bernie MS Excel MVP "David G Broadbent" wrote in message u... I had looked at this as well. Maybe I am not understanding something. Am creating a spreadsheet that will separate and analyse data from the first worksheet which is an import from a survey program I use. I then have other worksheets for the demographic questions which shall instantly do all the "work". They also draw the charts I want. this is then linked to a Word report format directly. I have achieved all of this for the survey data as a whole. Once I have the syntax for harvesting the demographics and then populate the necessary cells it should all be automatic (with no input from me) once I import the data sheet into the first worksheet. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... David, Do yourself a favor, and learn how to use Pivot Tables. You will be able to answer all your combinations of questions without using a single formula. Select your table, choose Data / Pivot Table... and click through to the end. Then drag Sex, Age, Range Employment Level, Work Location, Length of Service to the Row Fields area, and drag "Management operates an open door policy when it comes to safety." to the columns field and then again to the data field. And then you can hide categories or subcategories to get a score count of any combination of variables. HTH, Bernie MS Excel MVP "David G Broadbent" wrote in message u... Dear ALL, I have an excel sheet similar to below Sex Age Range Employment Level Work Location Length of Service Management operates an open door policy when it comes to safety. 2 4 1 2 1 1 3 1 5 2 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 2 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 5 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 2 4 1 2 1 1 3 1 5 5 2 3 3 1 4 1 5 3 1 5 2 3 3 4 5 The first 5 questions are demographic questions. The last is an "opinion" question. If somebody can help me with the syntax for getting one of these right I shall be eternally grateful!!!! WHAT I NEED TO DO: I need to be able to count the number of "1's" in the column "Management etc" dependant on the response in a demographic column eg "sex". Eg If the response is "1" in sex (meaning male) and '5" in management (meaning strongly agree); then count the number of "5's" only. The result in the target cell returned to me only delivers the males that strongly agreed. The correct answer to this example should be 6. Once I have this syntax I am thinking I just then modify it for all the other demographic questions. MANY THANKS. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting responses - please help | Excel Worksheet Functions | |||
Looking for experience with counting and conditions in excel 2003 | Excel Worksheet Functions | |||
Need Formulas for counting multiple conditions | Excel Worksheet Functions | |||
Counting Unique Records with multiple conditions | Excel Worksheet Functions | |||
Counting occurrences of multiple conditions | Excel Discussion (Misc queries) |