Counting the number of Agrees and Disagrees depending on what department someone belongs to

[Soni123456]

Hi all,

Was wondering if someone could help me with this. I have a list of participants all belonging to different departments and they have all answered a number of items using the ratings "Strongly agree, Agree, No opinion, Disagree, Strongly disagree". I want to be able to count the number of agrees and strongly agrees for people belonging to only one department (i.e. HR).

A B C D E F
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Department	Q1	Q2	Q3	Q4	Q5
HR	Agree	Strongly Agree	Agree	Disagree	Strongly Agree
Finance	No opinion/ Not applicable	Disagree	No opinion/ Not applicable	Agree	Agree
IT	Disagree	Agree	Agree	Agree	Disagree
IT	Agree	Strongly Agree	Agree	Agree	Strongly Agree
IT	No opinion/ Not applicable	Agree	Agree	No opinion/ Not applicable	Agree
Finance	Agree	Strongly Agree	Strongly Agree	Agree	Agree
HR	Agree	Agree	Disagree	Agree	Disagree
HR	Agree	Agree	Disagree	Disagree	Disagree
HR	Agree	Agree	Disagree	Disagree	Disagree
Finance	Agree	Strongly Agree	Agree	Agree	Strongly Agree
Finance	No opinion/ Not applicable	Agree	Agree	No opinion/ Not applicable	Agree

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

I have used this formula but it doesn't seem to work

=if(A2:A12="HR",(countifs(B2:F12,"Strongly agree",B2:F12,"Agree"),""))

Thank you SO much in advance

 

[Weazel]

maybe something like...

=SUMPRODUCT((A2:A12="HR")*(B2:F12="Agree"))

 

[Rick Rothstein]

maybe something like...

=SUMPRODUCT((A2:A12="HR")*(B2:F12="Agree"))

More like this I believe...

=SUMPRODUCT((A2:A12="HR")*((B2:F12="Agree")+(B2:F12="Strongly Agree")))

 

[Soni123456]

Perfect!! It worked! Thank you so much!!