Count Once Based on Criteria and Another Column

birkc21

New Member
Joined
Aug 23, 2019
Messages
3
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.
Fu8qO.png
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top