Google Sheets – COUNTIFS with OR Over a Range

google sheets

My spreadsheet is as follows (sorry about the crude setup).

    A         B     C      D       E       F         G
1  Order     FB?   TW      G+     YT      LI     Frequency?
2   1         x     -      x      -        x        daily
3   2         -     -      -      x        x        monthly
4   3         -     -      -      -        x        daily
5   4         x     x      -      -        x        never
6   5         -     -      -      -        -        never

 +1000 more rows with the same logic

On another sheet, I'm using COUNTIFS to extract the total number of those with daily, monhtly, etc frequency of publishing. Now, to my problem.

Notice how in the sixth row nothing is marked? My problem is that this isn't the only row with that setup, and, as far as I can tell by skimming the filtered table, it only happens in the rows with the frequency of never.

I would like to make a COUNTIFS which would look at the rows with the frequency of never and check if there's no x in any of the B-F columns for the corresponding row. These values shouldn't be counted. Or, to put things differently, it should only count a row with the frequency of never if there's at least one x in any of the B-F columns for the current row.

I'm guessing I should use a combination of COUNTIFS and OR, but I'm not sure how.

How can I achieve that?

Best Answer

Crude but simple, please try:

=countifs(G:G,"never",B:B,"<>x",C:C,"<>x",D:D,"<>x",E:E,"<>x",F:F,"<>x")

There may be "better" answers (eg that don't apply countifs) but possibly depending upon whether your dashes are in your spreadsheet or just included above for presentation. For example, if those nevers are not to be counted you might consider whether you could filter to select those rows and delete them (presumably would leave gaps in Order however).

Leave out the first pair (G:G,"never",) and it count rows without any x regardless of what is in ColumnG.

Related Topic