Questionaire with yes/no responses
I need to count "Yes" from multiple columns and divide it by any cell in those columns which have text. It also needs to be sorted by multiple criteria.
This is the formula I am using but it is giving me a invalid data type error. I don't believe my OR function is being used correct but I'm not sure how to correct it.
This is the formula I have right now:
=IFERROR((COUNTIFS(OR({BWC 1 Cap}, "Yes", {BWC 2 Cap}, "Yes", {BWC 3 Cap}, "Yes", {BWC 4 Cap}, "Yes"), {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3)) / (COUNTIFS(OR({BWC 1 Cap}, ISTEXT(@cell), {BWC 2 Cap}, ISTEXT(@cell), {BWC 3 Cap}, ISTEXT(@cell), {BWC 4 Cap}, ISTEXT(@cell)), {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3)), "")
Answers

Hi @ARodman
Can I clarify, do you want to count each individual instance of "yes" and text? For example, one row with 4 "Yes" selections would = 4. One row with 2 "Yes" selections would = 2.
OR
Do you want to count any row that has at least 1 yes as 1. So one row with 4 "Yes" selections = 1, and a row with 1 "Yes" selection also = 1?
Option 1:
If you want to count each individual Yes, you can add multiple COUNTIFs together (same with the text search):
=IFERROR(
(COUNTIFS({BWC 1 Cap}, "Yes", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3) + COUNTIFS({BWC 2 Cap}, "Yes", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3) + COUNTIFS({BWC 3 Cap}, "Yes", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3) + COUNTIFS({BWC 4 Cap}, "Yes", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3))
/
(COUNTIFS({BWC 1 Cap}, <> "", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3) + COUNTIFS({BWC 2 Cap}, <> "", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3) + COUNTIFS({BWC 3 Cap}, <> "", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3) + COUNTIFS({BWC 4 Cap}, <> "", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3)), "")
Option 2:
If you're looking for the latter option, I would actually recommend using two helper columns in the source sheet to sort out the OR statement first, before the cross sheet formula. For example:
=IF(OR([1 Cap]@row = "Yes", [2 Cap]@row = "Yes", [3 Cap]@row = "Yes", [4 Cap]@row = "Yes"), "At least one Yes", "No Yes answers")
=IF(OR([1 Cap]@row <> "", [2 Cap]@row <> "", [3 Cap]@row <> "", [4 Cap]@row <> ""), "At least one Text", "All Blank")
Then you can use these two helper columns in your crosssheet reference:
=IFERROR(COUNTIFS({Helper 1  Yes or No}, "At least one Yes", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3) / COUNTIFS({Helper 2  Text or Blank}, "At least one Text", {BWC Div}, C7, {BWC Month}, $A$2, {BWC Year}, $A$3), "")
If I've misunderstood your request, it would be helpful to see a screen capture of your source sheet, explaining how you want the rows to be counted, but please block out sensitive data.
Cheers,
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 217 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!