Count if certain criteria excluding duplicates

Sfoster924

New Member
Joined
Jun 28, 2022
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi All!
I am trying to create a formula where I can count unique "closed dates" (column D) based on if the Next steps " (column c) and "Group Name" are unique (No duplicates) with out using the remove duplicates function. I need the information for other aspects of the report.

In other words I want to know how many unique "close dates" occurred by each group name for all different options in the "next steps" Column. This report has several lines for the same close date and I want to count those as only 1 instance.

For Group F- The correct output would be : 1 cross functional meeting and 2 Drop in visit meeting ( I am ok with a formula for each type of meeting)
For group A - The correct output would be : 1 cross functional meeting because only one can be counted per day
1656449858465.png
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Welcome to the MrExcel forum!

Try:

Book3
ABCDEFGHI
1Group NameNext StepsClosed DateGroupNext StepsUnique Dates
2AStanding Cross Functional Team Meeting6/21/2022FStanding Cross Functional Team Meeting1
3AStanding Cross Functional Team Meeting6/21/2022Drop-in Visit/Meeting2
4BStanding Cross Functional Team Meeting6/21/2022
5BStanding Cross Functional Team Meeting6/21/2022
6BStanding Cross Functional Team Meeting6/21/2022
7BStanding Cross Functional Team Meeting6/21/2022
8BStanding Cross Functional Team Meeting6/21/2022
9CStanding Cross Functional Team Meeting6/22/2022
10DStanding Cross Functional Team Meeting6/24/2022
11EDrop-in Visit/Meeting
12EStanding Cross Functional Team Meeting
13FDrop-in Visit/Meeting6/22/2022
14FDrop-in Visit/Meeting6/23/2022
15FStanding Cross Functional Team Meeting6/24/2022
16FStanding Cross Functional Team Meeting6/24/2022
17Group NameDrop-in Visit/Meeting6/22/2022
18HDrop-in Visit/Meeting
19
20
Sheet23
Cell Formulas
RangeFormula
I2:I3I2=SUM(SIGN(FREQUENCY(IF(($B$2:$B$20=$G$2)*($C$2:$C$20=H2),$D$2:$D$20),$D$2:$D$20)))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thank you this works for a single group's count. I am actually trying to get an overall count for all groups listed in column B for the "next steps" with unique close dates. Is that possible?
So total Drop in visits and cross functional team meetings with unique close dates and group names (no duplicates). I have over 3000 rows of data so I need to use whole column references.
 
Upvote 0
Consider:

Book3
ABCDEFGHI
1Group NameNext StepsClosed DateGroupStanding Cross Functional Team MeetingDrop-in Visit/Meeting
2AStanding Cross Functional Team Meeting6/21/2022A10
3AStanding Cross Functional Team Meeting6/21/2022B10
4BStanding Cross Functional Team Meeting6/21/2022C10
5BStanding Cross Functional Team Meeting6/21/2022D10
6BStanding Cross Functional Team Meeting6/21/2022E11
7BStanding Cross Functional Team Meeting6/21/2022F12
8BStanding Cross Functional Team Meeting6/21/2022G01
9CStanding Cross Functional Team Meeting6/22/2022H01
10DStanding Cross Functional Team Meeting6/24/2022   
11EDrop-in Visit/Meeting   
12EStanding Cross Functional Team Meeting   
13FDrop-in Visit/Meeting6/22/2022   
14FDrop-in Visit/Meeting6/23/2022   
15FStanding Cross Functional Team Meeting6/24/2022   
16FStanding Cross Functional Team Meeting6/24/2022   
17GDrop-in Visit/Meeting6/22/2022   
18HDrop-in Visit/Meeting   
19
20
Sheet23
Cell Formulas
RangeFormula
G2:G18G2=IF(G1="","",IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$20)/(COUNTIF(G$1:G1,$B$2:$B$20)=0)/($B$2:$B$20<>""),1)),""))
H2:I18H2=IF($G2="","",SUM(SIGN(FREQUENCY(IF(($B$2:$B$20=$G2)*($C$2:$C$20=H$1),$D$2:$D$20),$D$2:$D$20))))
Press CTRL+SHIFT+ENTER to enter array formulas.


I'm guessing how you want the results presented. If this doesn't suit, please give an example what you want. I used the XL2BB add-in to create this mini-sheet. See the link in my signature or the reply box for how to use it.

As far as the whole column references, these are array formulas, and very calculation intensive. Using a whole column reference could bog down your sheet considerably. If you have 3000 lines of data, I'd suggest using a bottom row of say, 10000. Since Excel has over 1,000,000 rows, that cuts the calculations down by 99%.

Also note that a blank date (group E) counts as 1. I can adjust that if need be, at the cost of a longer formula.
 
Upvote 0
Solution

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

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