如何使用CONCATENATE、COUNTIFS和OR函数？统计空白或n/a的公式修正问题

Got it, let's sort out this formula issue! The problem with your original =CONCATENATE(COUNTIFS(range,"n/a",range,""),"/16") is that COUNTIFS uses AND logic by default—it only counts cells that are both "n/a" AND blank at the same time (which is impossible, so you were probably getting 0 every time).

To get the OR behavior you want (count cells that are either "n/a" OR blank), you'll need to split the conditions into separate COUNTIF functions and add their results together. Here's how to adjust it:

Option 1 (Sticking with CONCATENATE) #

=CONCATENATE(COUNTIF(range,"n/a") + COUNTIF(range,""), "/16")

Option 2 (Simpler Text Concatenation with &) #

Excel lets you use the & operator to combine text directly, which is often cleaner than wrapping everything in CONCATENATE:

=COUNTIF(range,"n/a") + COUNTIF(range,"") & "/16"

Quick Breakdown #

• COUNTIF(range,"n/a") counts all cells in your range containing the text "n/a"
• COUNTIF(range,"") counts all completely blank cells in the range
• Adding these two numbers gives you the total count of cells that meet either condition (pure OR logic)
• The & "/16" (or CONCATENATE) combines that total with "/16" to match your desired output format

Note: This works if "n/a" is plain text in your cells. If you're dealing with Excel's native #N/A error value instead, swap the "n/a" condition with COUNTIF(range,NA()).

内容的提问来源于stack exchange，提问作者ASE