Home > other >  If row contains any value then add one to counter cell
If row contains any value then add one to counter cell

Time:10-27

I have two main response options in a survey question: A and B, and a handful of secondary options: D, E, F.

In my data export I'll have five columns. In a separate sheet I am using COUNTIFS to get totals, but am stumped as to the best way to deal with the secondary options. Essentially, if columns D, E and F contain ANY value I would like to add 1 to a counter cell. If columns D, E and F ALL contain values I still only want this to count as one (think of it like those three comprise the "other" category and don't need to be SUMMED as individual parts).

CodePudding user response:

try:

=INDEX(IF((D2:D<>"") (E2:E<>"") (F2:F<>""), 1, 0))

enter image description here

and then just sum it to get count like:

=SUM(INDEX(IF((D2:D<>"") (E2:E<>"") (F2:F<>""), 1, 0)))
  • Related