COUNTIFS with LEN and more conditions

user_9602540 0
Viewed 1422

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.

countif google-sheets-formula counting google-sheets
3 Answers

You can do it with SUMPRODUCT

Sample:

=SUMPRODUCT(Database!C:C=true, LEN(Database!B:B)=17)
user_115997890

use:

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

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