1

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?

0

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.

1
  • 1
    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. – FiddlingAway Apr 25 '17 at 8:36
0

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
B$1:F$1 the Header Row (FB-->LI)
$1:$1000 all the Data rows the same number as G1:G1000 in order to let Offset read all
SIGN to return 1 if TRUE
Countif with offset to count 1 each row that contains "X" (1 or several X)
Sumproduct will sum the 1

2
  • Will try your solution, and get back to you. Sorry for taking this long to reply. – FiddlingAway Apr 25 '17 at 11:17
  • Unfortunately, this solution did not work for me - there was always a slight difference in the values which I had obtained by counting manually and using pnuts' solution, on one side, and the one I received with your function, on the other. – FiddlingAway May 10 '17 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.