Count if certain criteria excluding duplicates

[Sfoster924]

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

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D	E	F	G	H	I
1		Group Name	Next Steps	Closed Date			Group	Next Steps	Unique Dates
2		A	Standing Cross Functional Team Meeting	6/21/2022			F	Standing Cross Functional Team Meeting	1
3		A	Standing Cross Functional Team Meeting	6/21/2022				Drop-in Visit/Meeting	2
4		B	Standing Cross Functional Team Meeting	6/21/2022
5		B	Standing Cross Functional Team Meeting	6/21/2022
6		B	Standing Cross Functional Team Meeting	6/21/2022
7		B	Standing Cross Functional Team Meeting	6/21/2022
8		B	Standing Cross Functional Team Meeting	6/21/2022
9		C	Standing Cross Functional Team Meeting	6/22/2022
10		D	Standing Cross Functional Team Meeting	6/24/2022
11		E	Drop-in Visit/Meeting
12		E	Standing Cross Functional Team Meeting
13		F	Drop-in Visit/Meeting	6/22/2022
14		F	Drop-in Visit/Meeting	6/23/2022
15		F	Standing Cross Functional Team Meeting	6/24/2022
16		F	Standing Cross Functional Team Meeting	6/24/2022
17		Group Name	Drop-in Visit/Meeting	6/22/2022
18		H	Drop-in Visit/Meeting
19
20
Sheet23

Cell Formulas
Range	Formula
I2:I3	I2	=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.

 

[Sfoster924]

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.

 

[Eric W]

Consider:

Book3
	A	B	C	D	E	F	G	H	I
1		Group Name	Next Steps	Closed Date			Group	Standing Cross Functional Team Meeting	Drop-in Visit/Meeting
2		A	Standing Cross Functional Team Meeting	6/21/2022			A	1	0
3		A	Standing Cross Functional Team Meeting	6/21/2022			B	1	0
4		B	Standing Cross Functional Team Meeting	6/21/2022			C	1	0
5		B	Standing Cross Functional Team Meeting	6/21/2022			D	1	0
6		B	Standing Cross Functional Team Meeting	6/21/2022			E	1	1
7		B	Standing Cross Functional Team Meeting	6/21/2022			F	1	2
8		B	Standing Cross Functional Team Meeting	6/21/2022			G	0	1
9		C	Standing Cross Functional Team Meeting	6/22/2022			H	0	1
10		D	Standing Cross Functional Team Meeting	6/24/2022
11		E	Drop-in Visit/Meeting
12		E	Standing Cross Functional Team Meeting
13		F	Drop-in Visit/Meeting	6/22/2022
14		F	Drop-in Visit/Meeting	6/23/2022
15		F	Standing Cross Functional Team Meeting	6/24/2022
16		F	Standing Cross Functional Team Meeting	6/24/2022
17		G	Drop-in Visit/Meeting	6/22/2022
18		H	Drop-in Visit/Meeting
19
20
Sheet23

Cell Formulas
Range	Formula
G2:G18	G2	=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:I18	H2	=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.

 

[Sfoster924]

This helps! Thankyou!