Forum Discussion
Counting mix of text and numbers
I'm currently using this formula: =COUNT('All Defects'!A:A) to count cells that contain a numeric value. How do I revise this formula to count a mix of text and numbers?
Then use something like
=COUNTA('All Defects'!A2:A10000)
or if you prefer
=COUNTA('All Defects'!A:A)-1
5 Replies
LisaSingleton As Hans stated, you can use COUNTA() to count everything but it will also count empty string returned by any function. To get rid from that situation you can use-
=SUM(--('All Defects'!A:A<>""))Or COUNTA() like-
=COUNTA('All Defects'!A:A)- Thanks! Both of these formulas count the defects but also add the column heading.
Then use something like
=COUNTA('All Defects'!A2:A10000)
or if you prefer
=COUNTA('All Defects'!A:A)-1
Use COUNTA instead of COUNT.
COUNTA(range) returns the number of non-blank cells in range.
Remark: a cell that contains a formula that returns "" counts as non-blank even though it looks blank.
