Count Once Based on Criteria and Another Column

[birkc21]

I am trying to create a formula that will count the number of "Overdue" people instead of "Overdue" cells. Each person has the multiple rows with their name in it. The criteria to determine if it's "overdue" is:


-Column F and G are not within 3 years


-Column G are blank


-Column F = 0 and Column G are not within 3 years


The following code counts the criteria accurately. And doesn't account for duplicate names.


`=SUM(COUNTIFS(TT[Fiscal Law 301 CBT],"<"&TODAY()-1065,TT[Fiscal Law 301 CBT],">"&TODAY()-1095,TT[Fiscal Law In-Residence],"<"&TODAY()-1065,TT[Fiscal Law In-Residence],">"&TODAY()-1095),COUNTIFS(TT[Fiscal Law In-Residence],"",TT[Fiscal Law 301 CBT],"<"&TODAY()-1065,TT[Fiscal Law 301 CBT],">"&TODAY()-1095))`


The formula I want should count based on the criteria above and check `column Z` `TT[Name]` for duplicates and count them only once. And also count unique values once.

 

[jorismoerings]

Hi,

Try this

`=SUM(sum(1/COUNTIFS(TT[Fiscal Law 301 CBT],"<"&TODAY()-1065,TT[Fiscal Law 301 CBT],">"&TODAY()-1095,TT[Fiscal Law In-Residence],"<"&TODAY()-1065,TT[Fiscal Law In-Residence],">"&TODAY()-1095,TT[Name],TT[Name])),SUM(1/COUNTIFS(TT[Fiscal Law In-Residence],"",TT[Fiscal Law 301 CBT],"<"&TODAY()-1065,TT[Fiscal Law 301 CBT],">"&TODAY()-1095,TT[Name],TT[Name])))`

and confirm it with Ctrl-Shft-Enter as it's an array formula.

 

[birkc21]

Thank you for the reply, I pasted the formula in the cell, confirmed it wit CSE, and it returned the error #DIV/0! meaning cannot did by zero.

 

[birkc21]

Thank You for the reply, I pasted the formula in the cell, confirmed it with CSE, and it returned the error #DIV/0! meaning cannot divide by zero.

 

[jorismoerings]

Hi,

That's possible. In fact your formula sums the result of 3 countifs functions. I added the unique name in all 3 and by doing that it's possible one of the COUNTIFS comes up with 0.
Because the COUNTIFS are the divider you need to sort out which one is causing the #DIV/O error or add a IFERROR statement to each SUM statement.