Excel Magic Trick 1181: Formula to Count Cells with 4 or More Characters: COUNTIFS & SUMPRODUCT

See how to:
1. (00:08) Count numbers with 4 or more characters using COUNTIFS function. See that COUNTIFS requires Comparative Operators in Double Quotes.
2. (02:00) Count text cells that contain 4 or more characters using LEN and SUMPRIDUCT functions, array operations, and see that direct array operations do not require Double Quotes around comparative operators. Also see how double negatives can convert TRUEs and FALSEs (Boolean values) to ones and zeros, 1s and 0s.

[excelisfun]

Excel Magic Trick 1181: Formula to Count Cells with 4 or More Characters: COUNTIFS & SUMPRODUCT
See how to:
1. (00:08) Count numbers with 4 or more characters using COUNTIFS function. See that COUNTIFS requires Comparative Operators in Double Quotes.
2. (02:15) Count text cells that contain 4 or more characters using LEN and SUMPRIDUCT functions, array operations, and see that direct array operations do not require Double Quotes around comparative operators. Also see how double negatives can convert TRUEs and FALSEs (Boolean values) to ones and zeros, 1s and 0s.

[MySpreadsheetLab]

Thanks Mike. Sumproduct function is so versatile! Related to this is the lesser known Product function. I used it last week.

[BillSzysz]

Thanks :-)
What do you think about this one for letters....
=COUNTIF(C8:C33, REPT("?", C3)&"*")
and for numbers
=COUNTIFS(A8:A33, ">="&10^(A3-1))
where A3 contain number of digits we want.

[tomash]

About array formulas I once needed to use countif as a part of more complicated formula to get a single number as an argument in the formula. I though array formulas enable me to put as a criterium in countif expanding range of cells as the formula is copied down. Unfortunately didn't work

[trbone]

Why does this only work with sumproduct and not just plain sum? Is that because you're only simulating the column and sum can't handle that?