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

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

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
Asked:
Andreamary
1 Solution
 
Glenn RayExcel 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
 
AndreamaryAuthor Commented:
Perfect...thanks very much, Glenn!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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