CODEWITHSUNDEEP
excelexcel-formula
MTplus
MTplus

Reputation: 2391

how can I count number of rows that contains text/numbers in excel

I want to count all rows in a range that contains either text or numbers, what formula can I use for that? I tried this but it does not count correct...

=COUNTIF(B2:B50;"*") + SUMPRODUCT( -- ISTEXT(B2:B50))

data/text in cells can be like this...

    S
    160
    XS
    M

Upvotes: 1

Views: 677

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

To make your original formulas work.

You can use

=COUNTIF(B2:B50,"<>")

or

=SUMPRODUCT(--(B2:B50<>""))

However, @JvdV formula is more appropriate. If you need to ignore formula blanks then the SUMPRODUCT formula can be used.

Upvotes: 1

JvdV
JvdV

Reputation: 75960

To count both text and numbers, use COUNTA > =COUNTA(B2:B50)

Be aware that this does also count empty formulas!

Upvotes: 1

Related Questions