Questionaire with yes/no responses

ARodman

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)), "")

Genevieve P.

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 cross-sheet 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