# 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
Asked:
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Perfect...thanks very much, Glenn!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.