# Countifs with OR over a range

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?

=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.

• Your formula works as I had originally wanted it to. Thanks. Regarding the dashes - they are indeed there. Also, this is just a part of my table. The original has 30+ columns with additional data, so I can't really delete them. I need the formula so I can filter out the entry mistakes - I've talked to the folks who've been manually adding the data, and all of them have confirmed that they've typed never / selected it from data validation, even if none of the important columns (FB, TW, G+ etc) were not x-ed. Apr 25, 2017 at 8:36

For counting the Rows of Frequency = "never" and containing "X" (1 or several will be counting 1) You can use the following:

=SUMPRODUCT(SIGN(G1:G1000="never")*SIGN(COUNTIF(OFFSET(B\$1:F\$1, ROW(\$1:\$1000)-1, 0), "X")))

G1:G1000 the column of Frequency