[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 205

# Revising an array formula to exclude blanks or cells with "0"

I would like a count of the unique 4-digit characters in Column A where Column B contains initials + date only (excluding instances of initials + date + N/A).

I was using this array formula below which was working well as long as Column B did not have any blanks or cells with "0":

{=SUM(IF(ISNUMBER(MATCH(ROW(INDIRECT("1:10000")),IF(RIGHT(\$B\$3:\$B\$81,3)<>"N/A",\$A\$3:\$A\$81+0),0)),1))}

A few days into using the formula, I've realized that there will be cases when there are blanks or cells with "0" in Column B (and Column A for that matter), and the current array formula is including them in its calculation as valid. So instead of a result of 15 instances, the formula is indicating 58 instances.

I have included a sample spreadsheet to illustrate this.

Is there an adjustment that can be made to the formula to ensure that blanks and zeros in either column are not included in the total?

Thanks,
Andrea
Book2.xlsm
0
Andreamary
1 Solution

Excel VBA DeveloperCommented:
Here's the modified array formula that will ignore zero values in column B:
=SUM(IF(ISNUMBER(MATCH(ROW(INDIRECT("1:10000")),IF(RIGHT(\$B\$3:\$B\$81,3)<>"N/A",IF(\$B\$3:\$B\$81<>0,\$A\$3:\$A\$81+0,0),0),0)),1,0))

If there is a blank value in B, it won't be counted either.

Regards,
-Glenn
0

Author Commented:
Perfect...thanks very much, Glenn!
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.