Count rows with same text but only those with a different date

[SaraWitch]

Hello everyone.

I am looking for a formula that will count rows with same text, but only those with a different date. For example, Breakfast Club = 3, Cooking = 2, Bowling = 1:

RSI Activities WEST.xlsm
	A	B	C	D	E	F	G	H
1		RSI Activities and Training WEST - Demographic
2		Summary
3
4		Most Attended Activity:	Breakfast Club, Cooking			Number of Females:		9
5		Most Common Housing Status:	Rough sleeping, HF			Number of Males:		2
6		Services Most Known To:	Centre only, Homeworks			Number of Transgender:		1
7		Age Range Most Attended:	18 - 25, 26 - 35			Total:		12
8
9		Type of Activity	Gender	Age Range	Housing Status	Services Known To	Date	Attendance Number
10
11		Breakfast Club	Female	18 - 25	Rough sleeping	Centre only	01/04/2020	1
12		Breakfast Club	Female	26 - 35	Rough sleeping	Centre only	01/04/2020	1
13		Breakfast Club	Male	18 - 25	Rough sleeping	Centre only	01/05/2020	1
14		Breakfast Club	Transgender	18 - 25	Rough sleeping	Centre only	01/08/2020	1
15		Breakfast Club	Female	18 - 25	Rough sleeping	Centre only	01/08/2020	1
16		Cooking	Female	18 - 25	Rough sleeping	Centre only	01/06/2020	1
17		Cooking	Female	26 - 35	HF	Homeworks	01/06/2020	1
18		Cooking	Female	18 - 25	HF	Homeworks	01/06/2020	1
19		Cooking	Female	26 - 35	HF	Homeworks	01/08/2020	1
20		Cooking	Female	26 - 35	HF	Homeworks	01/08/2020	1
21		Bowling	Female	26 - 35	HF	Homeworks	01/09/2020	1
22		Bowling	Male	26 - 35	HF	Homeworks	01/09/2020	1
Demographics

Cell Formulas
Range	Formula
C4	C4	=IFERROR(INDEX($B$11:$B$6301,MIN(MODE.MULT(IF($B$11:$B$6301<>"",MATCH($B$11:$B$6301,$B$11:$B$6301,)))))&IFERROR(", "&INDEX($B$11:$B$6301,SMALL(MODE.MULT(IF($B$11:$B$6301<>"",MATCH($B$11:$B$6301,$B$11:$B$6301,))),2)),"")&IFERROR(", "&INDEX($B$11:$B$6301,SMALL(MODE.MULT(IF($B$11:$B$6301<>"",MATCH($B$11:$B$6301,$B$11:$B$6301,))),3)),""),"")
C5	C5	=IFERROR(INDEX($E$11:$E$6301,MIN(MODE.MULT(IF($E$11:$E$6301<>"",MATCH($E$11:$E$6301,$E$11:$E$6301,)))))&IFERROR(", "&INDEX($E$11:$E$6301,SMALL(MODE.MULT(IF($E$11:$E$6301<>"",MATCH($E$11:$E$6301,$E$11:$E$6301,))),2)),"")&IFERROR(", "&INDEX($E$11:$E$6301,SMALL(MODE.MULT(IF($E$11:$E$6301<>"",MATCH($E$11:$E$6301,$E$11:$E$6301,))),3)),""),"")
C6	C6	=IFERROR(INDEX($F$11:$F$6301,MIN(MODE.MULT(IF($F$11:$F$6301<>"",MATCH($F$11:$F$6301,$F$11:$F$6301,)))))&IFERROR(", "&INDEX($F$11:$F$6301,SMALL(MODE.MULT(IF($F$11:$F$6301<>"",MATCH($F$11:$F$6301,$F$11:$F$6301,))),2)),"")&IFERROR(", "&INDEX($F$11:$F$6301,SMALL(MODE.MULT(IF($F$11:$F$6301<>"",MATCH($F$11:$F$6301,$F$11:$F$6301,))),3)),""),"")
C7	C7	=IFERROR(INDEX($D$11:$D$6301,MIN(MODE.MULT(IF($D$11:$D$6301<>"",MATCH($D$11:$D$6301,$D$11:$D$6301,)))))&IFERROR(", "&INDEX($D$11:$D$6301,SMALL(MODE.MULT(IF($D$11:$D$6301<>"",MATCH($D$11:$D$6301,$D$11:$D$6301,))),2)),"")&IFERROR(", "&INDEX($D$11:$D$6301,SMALL(MODE.MULT(IF($D$11:$D$6301<>"",MATCH($D$11:$D$6301,$D$11:$D$6301,))),3)),""),"")
H4	H4	=IF(COUNTIF($C$11:$C$6301,"Female")=0,"",COUNTIF($C$11:$C$6301,"Female"))
H5	H5	=IF(COUNTIF($C$11:$C$6301,"Male")=0,"",COUNTIF($C$11:$C$6301,"Male"))
H6	H6	=IF(COUNTIF($C$11:$C$6301,"Transgender")=0,"",COUNTIF($C$11:$C$6301,"Transgender"))
H7	H7	=SUM($H$4:$H$6)
H11:H22	H11	=IF($B11>0,1,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

This data is captured on a Summary sheet, which I've played around with formulas, as you will see!

RSI Activities WEST.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Summary	RSI Activities and Training WEST - Summary
2		Most Attended Activity:	Breakfast Club, Cooking						Budget Amount		£10,000.00
3		Most Common Housing Status:	Rough sleeping, HF						Total Expenditure
4		Services Most Known To:	Centre only, Homeworks						Remaining Budget		£10,000.00
5		Age Range Most Attended:	18 - 25, 26 - 35
6
7		Activity	Total Number of Sessions Held	Number of Attendees
8				Apr-20	May-20	Jun-20	Jul-20		Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Total	01/04/2021	Unique list of dates
9		Bowling	#SPILL!	0	0	0	0		0	2	0	0	0	0	0	0	2		01/04/2020
10		Breakfast Club	5	2	1	0	0		2	0	0	0	0	0	0	0	5		01/05/2020
11		Cinema		0	0	0	0		0	0	0	0	0	0	0	0	0		01/08/2020
12		Cooking	5	0	0	3	0		2	0	0	0	0	0	0	0	5		01/06/2020
13		Exercise Class		0	0	0	0		0	0	0	0	0	0	0	0	0		01/09/2020
14		Games		0	0	0	0		0	0	0	0	0	0	0	0	0		00/01/1900
15		Gardening		0	0	0	0		0	0	0	0	0	0	0	0	0
16		Pottery Class		0	0	0	0		0	0	0	0	0	0	0	0	0
17		Women's Support		0	0	0	0		0	0	0	0	0	0	0	0	0
18		Other		0	0	0	0		0	0	0	0	0	0	0	0	0
19
20		Gender	Number
21		Female	9
22		Male	2
23		Transgender	1
24		Total	12
25
26		Age Range	Number
27		18 - 25	6
28		26 - 35	6
29		36 - 45
30		46 and over
31
32		Housing Status	Number
33		HF	6
34		PRS
35		Rough sleeping	6
36		Social
37		TA
38
39		Services Known To	Number
40		Centre only	6
41		Homeworks	6
42		RRP
43		RSI
44		None
Summary

Cell Formulas
Range	Formula
K2	K2	=IF(Finance!$C$2>0,Finance!$C$2,"")
K3	K3	=IF(Finance!$P$5>0,Finance!$P$5,"")
K4	K4	=IF(Finance!$C$7<>0,Finance!$C$7,"")
C2	C2	=Demographics!$C$4
C3	C3	=Demographics!$C$5
C4	C4	=Demographics!$C$6
C5	C5	=Demographics!$C$7
C9	C9	=IF(COUNTIF(Demographics!$G$11:$G$6301,$S:$S),COUNTIF(Demographics!$B$11:$B$6301,"Bowling"))
D9:F18,I9:O18	D9	=SUMPRODUCT(--(Demographics!$G$11:$G$6301<E$8), --(Demographics!$G$11:$G$6301>=D$8), --(Demographics!$B$11:$B$6301=Summary!$B9),Demographics!$H$11:$H$6301)
G9:G18,P9:P18	G9	=SUMPRODUCT(--(Demographics!$G$11:$G$6301<I$8), --(Demographics!$G$11:$G$6301>=G$8), --(Demographics!$B$11:$B$6301=Summary!$B9),Demographics!$H$11:$H$6301)
Q9	Q9	=SUMIF(Demographics!$B$11:$B$6301,"Bowling",Demographics!$H$11:$H$6301)
C10	C10	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Breakfast Club")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Breakfast Club"))
Q10	Q10	=SUMIF(Demographics!$B$11:$B$6301,"Breakfast Club",Demographics!$H$11:$H$6301)
C11	C11	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Cinema")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Cinema"))
Q11	Q11	=SUMIF(Demographics!$B$11:$B$6301,"Cinema",Demographics!$H$11:$H$6301)
C12	C12	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Cooking")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Cooking"))
Q12	Q12	=SUMIF(Demographics!$B$11:$B$6301,"Cooking",Demographics!$H$11:$H$6301)
C13	C13	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Exercise Class")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Exercise Class"))
Q13	Q13	=SUMIF(Demographics!$B$11:$B$6301,"Exercise Class",Demographics!$H$11:$H$6301)
C14	C14	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Games")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Games"))
Q14	Q14	=SUMIF(Demographics!$B$11:$B$6301,"Games",Demographics!$H$11:$H$6301)
C15	C15	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Gardening")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Gardening"))
Q15	Q15	=SUMIF(Demographics!$B$11:$B$6301,"Gardening",Demographics!$H$11:$H$6301)
C16	C16	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Pottery Class")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Pottery Class"))
Q16	Q16	=SUMIF(Demographics!$B$11:$B$6301,"Pottery Class",Demographics!$H$11:$H$6301)
C17	C17	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Women's Support")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Women's Support"))
Q17	Q17	=SUMIF(Demographics!$B$11:$B$6301,"Women's Support",Demographics!$H$11:$H$6301)
C18	C18	=IF(COUNTIF(Demographics!$B$11:$B$6301,"Other")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Other"))
Q18	Q18	=SUMIF(Demographics!$B$11:$B$6301,"Other",Demographics!$H$11:$H$6301)
S9:S21	S9	=IFERROR(INDEX(Demographics!$G$11:$G$6301,MATCH(0,INDEX(COUNTIF($S$8:S8,Demographics!$G$11:$G$6301),,),)),"")
C21	C21	=IF(COUNTIF(Demographics!$C$11:$C$6301,"Female")=0,"",COUNTIF(Demographics!$C$11:$C$6301,"Female"))
C22	C22	=IF(COUNTIF(Demographics!$C$11:$C$6301,"Male")=0,"",COUNTIF(Demographics!$C$11:$C$6301,"Male"))
C23	C23	=IF(COUNTIF(Demographics!$C$11:$C$6301,"Transgender")=0,"",COUNTIF(Demographics!$C$11:$C$6301,"Transgender"))
C24	C24	=SUM($C$21:$C$23)
C27	C27	=IF(COUNTIF(Demographics!$D$11:$D$6301,"18 - 25")=0,"",COUNTIF(Demographics!$D$11:$D$6301,"18 - 25"))
C28	C28	=IF(COUNTIF(Demographics!$D$11:$D$6301,"26 - 35")=0,"",COUNTIF(Demographics!$D$11:$D$6301,"26 - 35"))
C29	C29	=IF(COUNTIF(Demographics!$D$11:$D$6301,"36 - 45")=0,"",COUNTIF(Demographics!$D$11:$D$6301,"36 - 45"))
C30	C30	=IF(COUNTIF(Demographics!$D$11:$D$6301,"46 and over")=0,"",COUNTIF(Demographics!$D$11:$D$6301,"46 and over"))
C33	C33	=IF(COUNTIF(Demographics!$E$11:$E$6301,"HF")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"HF"))
C34	C34	=IF(COUNTIF(Demographics!$E$11:$E$6301,"PRS")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"PRS"))
C35	C35	=IF(COUNTIF(Demographics!$E$11:$E$6301,"Rough sleeping")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"Rough sleeping"))
C36	C36	=IF(COUNTIF(Demographics!$E$11:$E$6301,"Social")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"Social"))
C37	C37	=IF(COUNTIF(Demographics!$E$11:$E$6301,"TA")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"TA"))
C40	C40	=IF(COUNTIF(Demographics!$F$11:$F$6301,"Centre only")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"Centre only"))
C41	C41	=IF(COUNTIF(Demographics!$F$11:$F$6301,"Homeworks")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"Homeworks"))
C42	C42	=IF(COUNTIF(Demographics!$F$11:$F$6301,"RRP")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"RRP"))
C43	C43	=IF(COUNTIF(Demographics!$F$11:$F$6301,"RSI")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"RSI"))
C44	C44	=IF(COUNTIF(Demographics!$F$11:$F$6301,"None")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"None"))

Formula needs to work in Excel 2010 and Office 365.

Any help would be appreciated!

 

[Snakehips]

@SaraWitch
I suspect not in your preferred location but maybe something like....

Book1
	A	B	C
7		Activity	Total Number of Sessions Held
8	Unique Dated
9	1	Bowling	2
10	3	Breakfast Club	5
11	0	Cinema
12	2	Cooking	5
13	0	Exercise Class
14	0	Games
15	0	Gardening
16	0	Pottery Class
17	0	Women's Support
18	0	Other
19
Summary

Cell Formulas
Range	Formula
A9:A18	A9	{=SUMPRODUCT((Demographics!$B$11:$B$6301=B9)*(IFERROR(1/COUNTIFS(Demographics!$B$11:$B$6301,Demographics!$B$11:$B$6301,Demographics!$G$11:$G$6301,Demographics!$G$11:$G$6301),0)))}

Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces

References to B9 etc could be string, "Bowling" etc

Hope that helps.

 

[SaraWitch]

Amazing! Works exactly as I want it to (and I just put your formula in my Summary sheet column C - perfect!).

Thank you, Snakehips!