EXCEL: Count Range; but Exclude blanks AND a specific string

user_2347271 0
Viewed 335

Excel Version: Microsoft 365 bundle (V 2106 Build: 14131.20278)

I'm trying to create an Excel formula that outputs a number onto a single cell (B11); the formula must:

  • Count the number of cells that HAVE DATA on a given range [B2:B8];
  • Therefore, IT must NOT count non-blank cells; but must also NOT INCLUDE cells with a specific string (in this case the string would be "N/A") ;
  • The formula must also count the number of line breaks [so Char(10)] present on that given range.

I understand that it's not conventional to count inputted line breaks for a range; but that's what I need.

See the attached image:

B11: Count from B2:B8 | No Blanks, no "N/A" string

RANGE = In this example, the range of (non-blank AND non-"N/A") cells I want to count go from B2 to B8.

Cell w/ Formula: B11

As of now, I only have the following formula done.

=SUM(LEN(B2:B8)-LEN(B2:B8;CHAR(10);""))+(LEN(B2:B8)>0))

This formula outputs == "9" (i.e., 9 line breaks: ignores blanks, but includes the string "N/A")

This formula allows me to count all line breaks present on that given range (so, it doesn't count blank/empty cells), but it obviously doesn't exclude the specific string "N/A".

Expected output == "8" (i.e., 8 line breaks: ignoring blank cells AND cells with the string "N/A")

I suppose this could be accomplished with an "IF", but I haven't figured it out yet.

Thank you for your time!

PS: I tried to make a table in here, but I was not able to add line breaks inside a single cell; so, I ended up resorting to an image to explain my point, sorry about the inconvenience!)

excel-formula excel
3 Answers

Right, since you have got access to Microsoft 365, try the following:

=SUM(FILTER(1+(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,CHAR(10),""))),LEN(B2:B8)*(B2:B8<>"N/A"),0))

Nicely layed out question btw, and a fun little issue to work on. Hopefully the above helped you out.

user_97581940

Just for a different approach:
(edited after @ScottCraner pointed out it didn't account for N/A)

=LET(x,FILTER($B$2:$B$8,B2:B8<>"N/A"),
     y,TEXTJOIN(CHAR(10),TRUE,x),
     z,SUBSTITUTE(y,CHAR(10),""),
   LEN(y)-LEN(z)+1)

Of course, if you have a lot of data, TEXTJOIN will fail if the resultant string is > 32,767 characters

user_28729220

Should be able to do this with a simple COUNTIF (or COUNTIFS if you want to add more criteria to count against).

Try =COUNTIF($B$2:$B$8, "<>N/A")

EDIT1: As pointed out by cybernetic.nomad, a better approach to dealing with non-numeric data will be COUNTA. Another solution might be to just filter the whole data with something like:

=COUNTA(FILTER($B$2:$B$8, $B$2:$B$8<>"N/A"))

EDIT2: As pointed out by Scott Craner, this solution does not account for line breaks. You can add those in by using:

=COUNTA(FILTER($B$2:$B$8, $B$2:$B$8<>"N/A"))+
SUM(LEN($B$2:$B$8)-LEN(SUBSTITUTE($B$2:$B$8,CHAR(10),"")))
user_109627890
Related