If row contains any value then add one to counter cell-CodePudding

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

and then just sum it to get count like:

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