Forum Discussion
How to count if there is a yes in either of 2 columns
I'm a newbie at Excel and don't even know how to frame this question properly, so forgive me!
I have a spreadsheet of students enrolled in various classes. There is a separate column for each unit; if the student is enrolled in a unit they are marked Yes. There are some classes where 2 different units are being offered in the one session. What I want to be able to do is count how many are in a class by looking at how many students have a Yes in either of the 2 columns - so count the student only 1 time, whether it's Yes/blank, blank/Yes or Yes/Yes. I have no idea how to do this and would appreciate help!
If the data are in adjacent columns, for example E2:F100:
=SUM(BYROW(E2:F100, LAMBDA(r, --OR(r="Yes"))))
Alternatively, create a helper column, for example in column Z: if the data are in E2:E100 and G2:G100, enter
=--OR(E2="Yes", G2="Yes")
in Z2, and fill down to Z100. You can then use =SUM(Z2:Z100) for the count.
2 Replies
If the data are in adjacent columns, for example E2:F100:
=SUM(BYROW(E2:F100, LAMBDA(r, --OR(r="Yes"))))
Alternatively, create a helper column, for example in column Z: if the data are in E2:E100 and G2:G100, enter
=--OR(E2="Yes", G2="Yes")
in Z2, and fill down to Z100. You can then use =SUM(Z2:Z100) for the count.
Thank you so much, was able to cut and paste that formula and adjust my variables for a win!
