2

Is there a way to use COUNTIFS with LEN and more conditions. I want to count all rows that have a true value in column C and where the string in column B has a length of 17. So something like the snippet below but where the LEN()=17 condition actually works

COUNTIFS(Database!C:C;"=TRUE";Database!B:B;"LEN(Database!B:B)=17")

I am aware that I could use ARRAYFORMULA as suggested in Countif with len in Google Spreadsheet but try to do this without an additional column.

player0
  • 124,011
  • 12
  • 67
  • 124
phlprcks
  • 55
  • 1
  • 9
  • try `=IFNA(ROWS(FILTER(Database!C:C; Database!C:C = TRUE; LEN(Database!B:B) = 17)); 0)` – kishkin Mar 26 '20 at 12:44
  • Or with `COUNTIFS`: `=COUNTIFS(Database!C:C, TRUE, ARRAYFORMULA(LEN(Database!B:B)), 17)` (somehow didn't think of it at first) – kishkin Mar 26 '20 at 12:48
  • both did not work for me, one resultet in #NV the other in an error... but thanks for responding – phlprcks Mar 26 '20 at 13:33
  • 1
    that's because of `;` which you use because of your locale. Just change my commas `,` to semicolons. And the first one should've worked. There are semicolons actually. – kishkin Mar 26 '20 at 13:42

3 Answers3

3

You can do it with SUMPRODUCT

Sample:

=SUMPRODUCT(Database!C:C=true, LEN(Database!B:B)=17)
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • 1
    minor typing error: we need a ; instead of , so that would result in =SUMPRODUCT(Database!C:C=true; LEN(Database!B:B)=17) Thanks very much – phlprcks Mar 26 '20 at 13:31
  • 2
    For me it is `,`, and not `;`, probably it depends on your country settings. Glad it helped you! – ziganotschka Mar 26 '20 at 13:52
1

use:

=INDEX(COUNTIFS(Database!C:C; TRUE; LEN(Database!B:B); 17))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Hey, @player0! Is `INDEX` in this case better in any way than `ARRAYFORMULA`? Where did you learn about this behavior of `INDEX`? It does treat enclosed ranges the same as `SUMPRODUCT` (except for the actual multiplication and addition), right? Like `=SUMPRODUCT(COUNTIFS(Database!C:C, TRUE, LEN(Database!B:B), 17))` – kishkin Mar 26 '20 at 13:07
  • 1
    @kishkin no, its just shorter. INDEX is one of 4 array-type formulas – player0 Mar 26 '20 at 13:13
  • what is the fourth one? besides `ARRAYFORMULA`, `INDEX`, `SUMPRODUCT`. thank you – kishkin Mar 26 '20 at 13:47
  • 1
    ARRAYFORMULA, ARRAY_CONSTRAIN, INDEX, QUERY – player0 Mar 26 '20 at 13:54
0

try:

=COUNTIFS(Database!C:C; TRUE; ARRAYFORMULA(LEN(Database!B:B)); 17)

and an alternative filter solution:

=IFNA(ROWS(FILTER(Database!C:C; Database!C:C = TRUE; LEN(Database!B:B) = 17)); 0)
kishkin
  • 5,152
  • 1
  • 26
  • 40