CODEWITHSUNDEEP
excelexcel-formula
Aabo
Aabo

Reputation: 213

Count cells in excel with populated values

I have an excel sheet and I want to get a count of the cells by row which is not empty. Issue is that some of those consists of formulas and seems to be counted any way.

The cells for instance: are A1, A4, A6, A8, A10

And im trying to use the simple formula of

COUNTA(A1,A4,A6,A8,A10)

Can a correct my formula to disregard formulas and only count if the cell is actually populated with a number?

Upvotes: 0

Views: 342

Answers (2)

Rajput
Rajput

Reputation: 605

Put Range in formula instead of cells.

=SUMPRODUCT(--(LEN(A1:A13)>0))

and you can subtract like this:

=COUNTA(A1:A13)-COUNTIF(A1:A13,">""")

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152585

Use:

=SUMPRODUCT(--(CHOOSE({1,2,3,4,5},A1, A4, A6, A8, A10)<>""))

Upvotes: 0

Related Questions