0

I want to count the number of non-"blank" cells in a column. I have a column of cells with a concatenate() for their corresponding row, i.e.:

=CONCATENATE(I10,J10,K10)
=CONCATENATE(I11,J11,K11)
=CONCATENATE(I12,J12,K12)

Sometimes these rows are blank, so the concatenate returns "", but there's still a formula there so it's not actually an empty cell, so using :

=COUNTIFS(L1:L100,"<>"&"")

doesn't exclude the not-really-empty "blanks", and it just returns a count of the full range. Has anyone done anything like this before? Thank you in advanced.

Improve this question

3 Answers 3

Reset to default
1

COUNTIF accepts wild cards. So to count the non-blank entries in a range, try:

=COUNTIF(rngToCount,"?*")

All of the salmon colored cells in the screenshot below contain the CONCATENATE(… formula:

enter image description here

Improve this answer
1
  • That worked perfectly! Thank you!
    – MyFingerHertz
    Jun 27, 2020 at 3:52
1

Try to add auxiliary column with formula:

=IF(AND(ISFORMULA(A1)=TRUE,A1=""),1,0)

Tenter image description herehen use this formula:

=COUNT(A1:A6)+COUNTIF(B1:B6,1) enter image description here

Improve this answer
1
  • Works well enough as a workaround, thank you. Still curious if it can be done within the COUNTIFS function
    – MyFingerHertz
    Jun 26, 2020 at 20:44
0

You can count all the rows then subtract using COUNTBLANK. The fourth column in the image below has a concatenate function in it.

enter image description here

=COUNTA(F13:F15)-COUNTBLANK(F13:F15)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .