Sumproduct/countifs with mulitple criteria pair

[Mubasha_Qadri]

Hi


I have conducted a survey and data is according to following table.. I have data of more than 10K respondents. I want to count how many respondents are satisfied. then i want to apply different filter -criteria pair (one, two or more) to analyze the satisfaction across different groups....
For example

How many undergraduate respondents are satisfied.....
how many respondents from Lahore are satisfied...
How many undergraduate respondents from Lahore are satisfied (Or , AND)

I have tried countifs but formula become too lengthy for muliple nested if conditions....
Is there any short smart formula to meet the requirement...



[TABLE="width: 575"]
<colgroup><col style="width:76pt" width="101"> <col style="width:53pt" width="70"> <col style="width:63pt" width="84"> <col style="width:119pt" width="158"> <col style="width:51pt" width="68"> <col style="width:71pt" width="94"> </colgroup><tbody>[TR]
[TD="class: xl62, width: 101"]Level
[/TD]
[TD="class: xl62, width: 70"]Campus
[/TD]
[TD="class: xl62, width: 84"]Status
[/TD]
[TD="class: xl62, width: 158"]StudyProgram
[/TD]
[TD="class: xl62, width: 68"]CGPA
[/TD]
[TD="class: xl62, width: 94"]Question
[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied
[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Information Technology[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Not Sure[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Dissatisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Mass Communication[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Information Technology[/TD]
[TD="class: xl63"]3.00 - 4.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Not Sure[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
</tbody>[/TABLE]

 

[gvipin]

Hi, you can just use Pivot Table for your desired requirement

[TABLE="width: 242"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Count of Question[/TD]
[/TR]
[TR]
[TD]Masters[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Dissatisfied[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Not Sure[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Satisfied[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Very Satisfied[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]MS[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Satisfied[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Very Satisfied[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Undergraduate[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Not Sure[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Satisfied[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Very Satisfied[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]21[/TD]
[/TR]
</tbody>[/TABLE]

Hi


I have conducted a survey and data is according to following table.. I have data of more than 10K respondents. I want to count how many respondents are satisfied. then i want to apply different filter -criteria pair (one, two or more) to analyze the satisfaction across different groups....
For example

How many undergraduate respondents are satisfied.....
how many respondents from Lahore are satisfied...
How many undergraduate respondents from Lahore are satisfied (Or , AND)

I have tried countifs but formula become too lengthy for muliple nested if conditions....
Is there any short smart formula to meet the requirement...



[TABLE="width: 575"]
<tbody>[TR]
[TD="class: xl62, width: 101"]Level[/TD]
[TD="class: xl62, width: 70"]Campus[/TD]
[TD="class: xl62, width: 84"]Status[/TD]
[TD="class: xl62, width: 158"]StudyProgram[/TD]
[TD="class: xl62, width: 68"]CGPA[/TD]
[TD="class: xl62, width: 94"]Question[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Information Technology[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Not Sure[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Karachi[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Dissatisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Mass Communication[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Information Technology[/TD]
[TD="class: xl63"]3.00 - 4.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Not Sure[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Home[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]1.00 - 2.00[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]MS[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Masters[/TD]
[TD="class: xl63"]Islamabad[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Management[/TD]
[TD="class: xl63"]2.00 - 3.00[/TD]
[TD="class: xl63"]Satisfied[/TD]
[/TR]
[TR]
[TD="class: xl63"]Undergraduate[/TD]
[TD="class: xl63"]Lahore[/TD]
[TD="class: xl63"]Campus[/TD]
[TD="class: xl63"]Computer Science[/TD]
[TD="class: xl63"]<1[/TD]
[TD="class: xl63"]Very Satisfied[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mubasha_Qadri]

THanks for your reply. I have tried this but didn't find according to my requirement as there are more than 10 questions. I need out according to following format..

There are multiple filters and one can use one or more at the same time and get output matching the criteria

[TABLE="width: 555"]
<colgroup><col style="width:48pt" width="64"> <col style="width:71pt" width="94"> <col style="width:95pt" width="126"> <col style="width:60pt" width="80"> <col style="width:59pt" width="79"> <col style="width:84pt" width="112"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 94"][/TD]
[TD="width: 126"]Filters[/TD]
[TD="width: 80"][/TD]
[TD="width: 79"][/TD]
[TD="width: 112"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"]location[/TD]
[TD="class: xl65"]Lahore[/TD]
[TD]Optional[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"]program[/TD]
[TD="class: xl66"]Computer Science[/TD]
[TD]Optional[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"]level[/TD]
[TD="class: xl65"]Undergaduate[/TD]
[TD]Optional[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"]status[/TD]
[TD="class: xl65"]Home[/TD]
[TD]Optional[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"]CGPA[/TD]
[TD="class: xl65"][/TD]
[TD]Optional[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"]gender[/TD]
[TD="class: xl65"][/TD]
[TD]Optional[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"]Semester[/TD]
[TD="class: xl65"][/TD]
[TD]Optional[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]Q1[/TD]
[TD="class: xl62"]Very Satisfied[/TD]
[TD="class: xl62"]Satisfied[/TD]
[TD="class: xl62"]Not Sure[/TD]
[TD="class: xl62"]Dissatisfied[/TD]
[TD="class: xl62"]Very Dissatisfied[/TD]
[/TR]
[TR]
[TD="class: xl65"]Q2[/TD]
[TD="class: xl62"]2[/TD]
[TD="class: xl62"]4[/TD]
[TD="class: xl62"]0[/TD]
[TD="class: xl62"]0[/TD]
[TD="class: xl62"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Q3[/TD]
[TD="class: xl65"]3
[/TD]
[TD="class: xl65"]0
[/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]0
[/TD]
[TD="class: xl65"]0
[/TD]
[/TR]
[TR]
[TD="class: xl65"]Q4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Q1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Q2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]