Oct 05 2018 02:52 PM - edited Oct 08 2018 05:58 AM
I need help with figuring out how to write a formula. I have a spreadsheet with two columns containing data that I need to evaluate. The first column contains two dozen different department names. I want to search this column and if a row contains 1 of 5 specific departments I want to go to a second column that contains five rating scales (Strongly Agree, Agree, etc...), and if the row contain 2 specific rating scales I want to count it. I need a formula that finds multiple criteria in one column, and for true statements, looks in another column for multiple criteria, and counts the occurrences.
I've searched and tried sum(countfis, multiple countif,...nothing I've tried worked. Anyone have any ideas? Thank you in advance for your help.
Oct 05 2018 06:50 PM
Oct 08 2018 05:59 AM
Thank you for your response. I've uploaded an example. On the second tab I've provided details of what I'm trying to do. Please let me know if you have any other questions.
Oct 08 2018 06:40 AM
SolutionHi Vincent,
With two OR conditions you shall use columns conditional array in one case and rows array in another one, like
=SUM(COUNTIFS(Data!$A:$A,{"Commuter Operations","Facilities","FinOps","OPM/CHSA/COBRA","Vendor Management"},Data!$B:$B,{"Strongly Agree";"Agree"}))
More about that is here https://exceljet.net/formula/countifs-with-multiple-criteria-and-or-logic
Attcahed
Oct 13 2018 09:27 AM
Thank you! This solved my problem. I really appreciate your help.
Oct 08 2018 06:40 AM
SolutionHi Vincent,
With two OR conditions you shall use columns conditional array in one case and rows array in another one, like
=SUM(COUNTIFS(Data!$A:$A,{"Commuter Operations","Facilities","FinOps","OPM/CHSA/COBRA","Vendor Management"},Data!$B:$B,{"Strongly Agree";"Agree"}))
More about that is here https://exceljet.net/formula/countifs-with-multiple-criteria-and-or-logic
Attcahed