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

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDEFGH
1RSI Activities and Training WEST - Demographic
2Summary
3
4Most Attended Activity:Breakfast Club, CookingNumber of Females:9
5Most Common Housing Status:Rough sleeping, HFNumber of Males:2
6Services Most Known To:Centre only, HomeworksNumber of Transgender:1
7Age Range Most Attended:18 - 25, 26 - 35Total:12
8
9Type of ActivityGenderAge RangeHousing StatusServices Known ToDateAttendance Number
10
11Breakfast ClubFemale 18 - 25 Rough sleeping Centre only 01/04/20201
12Breakfast ClubFemale 26 - 35 Rough sleeping Centre only 01/04/20201
13Breakfast ClubMale 18 - 25 Rough sleeping Centre only 01/05/20201
14Breakfast ClubTransgender 18 - 25 Rough sleeping Centre only 01/08/20201
15Breakfast ClubFemale 18 - 25 Rough sleeping Centre only 01/08/20201
16CookingFemale 18 - 25 Rough sleeping Centre only 01/06/20201
17CookingFemale 26 - 35 HF Homeworks 01/06/20201
18CookingFemale 18 - 25 HF Homeworks 01/06/20201
19CookingFemale 26 - 35 HF Homeworks 01/08/20201
20CookingFemale 26 - 35 HF Homeworks 01/08/20201
21BowlingFemale 26 - 35 HF Homeworks 01/09/20201
22BowlingMale 26 - 35 HF Homeworks 01/09/20201
Demographics
Cell Formulas
RangeFormula
C4C4=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)),""),"")
C5C5=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)),""),"")
C6C6=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)),""),"")
C7C7=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)),""),"")
H4H4=IF(COUNTIF($C$11:$C$6301,"Female")=0,"",COUNTIF($C$11:$C$6301,"Female"))
H5H5=IF(COUNTIF($C$11:$C$6301,"Male")=0,"",COUNTIF($C$11:$C$6301,"Male"))
H6H6=IF(COUNTIF($C$11:$C$6301,"Transgender")=0,"",COUNTIF($C$11:$C$6301,"Transgender"))
H7H7=SUM($H$4:$H$6)
H11:H22H11=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
ABCDEFGHIJKLMNOPQRST
1SummaryRSI Activities and Training WEST - Summary
2Most Attended Activity:Breakfast Club, CookingBudget Amount£10,000.00
3Most Common Housing Status:Rough sleeping, HFTotal Expenditure 
4Services Most Known To:Centre only, HomeworksRemaining Budget£10,000.00
5Age Range Most Attended:18 - 25, 26 - 35
6
7ActivityTotal Number of Sessions HeldNumber of Attendees
8Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Total01/04/2021Unique list of dates
9Bowling#SPILL!000002000000201/04/2020
10Breakfast Club5210020000000501/05/2020
11Cinema 000000000000001/08/2020
12Cooking5003020000000501/06/2020
13Exercise Class 000000000000001/09/2020
14Games 000000000000000/01/1900
15Gardening 0000000000000 
16Pottery Class 0000000000000 
17Women's Support 0000000000000 
18Other 0000000000000 
19 
20GenderNumber 
21Female9 
22Male2
23Transgender1
24Total12
25
26Age RangeNumber
2718 - 256
2826 - 356
2936 - 45 
3046 and over 
31
32Housing StatusNumber
33HF6
34PRS 
35Rough sleeping6
36Social 
37TA 
38
39Services Known ToNumber
40Centre only6
41Homeworks6
42RRP 
43RSI 
44None 
Summary
Cell Formulas
RangeFormula
K2K2=IF(Finance!$C$2>0,Finance!$C$2,"")
K3K3=IF(Finance!$P$5>0,Finance!$P$5,"")
K4K4=IF(Finance!$C$7<>0,Finance!$C$7,"")
C2C2=Demographics!$C$4
C3C3=Demographics!$C$5
C4C4=Demographics!$C$6
C5C5=Demographics!$C$7
C9C9=IF(COUNTIF(Demographics!$G$11:$G$6301,$S:$S),COUNTIF(Demographics!$B$11:$B$6301,"Bowling"))
D9:F18,I9:O18D9=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:P18G9=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)
Q9Q9=SUMIF(Demographics!$B$11:$B$6301,"Bowling",Demographics!$H$11:$H$6301)
C10C10=IF(COUNTIF(Demographics!$B$11:$B$6301,"Breakfast Club")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Breakfast Club"))
Q10Q10=SUMIF(Demographics!$B$11:$B$6301,"Breakfast Club",Demographics!$H$11:$H$6301)
C11C11=IF(COUNTIF(Demographics!$B$11:$B$6301,"Cinema")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Cinema"))
Q11Q11=SUMIF(Demographics!$B$11:$B$6301,"Cinema",Demographics!$H$11:$H$6301)
C12C12=IF(COUNTIF(Demographics!$B$11:$B$6301,"Cooking")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Cooking"))
Q12Q12=SUMIF(Demographics!$B$11:$B$6301,"Cooking",Demographics!$H$11:$H$6301)
C13C13=IF(COUNTIF(Demographics!$B$11:$B$6301,"Exercise Class")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Exercise Class"))
Q13Q13=SUMIF(Demographics!$B$11:$B$6301,"Exercise Class",Demographics!$H$11:$H$6301)
C14C14=IF(COUNTIF(Demographics!$B$11:$B$6301,"Games")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Games"))
Q14Q14=SUMIF(Demographics!$B$11:$B$6301,"Games",Demographics!$H$11:$H$6301)
C15C15=IF(COUNTIF(Demographics!$B$11:$B$6301,"Gardening")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Gardening"))
Q15Q15=SUMIF(Demographics!$B$11:$B$6301,"Gardening",Demographics!$H$11:$H$6301)
C16C16=IF(COUNTIF(Demographics!$B$11:$B$6301,"Pottery Class")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Pottery Class"))
Q16Q16=SUMIF(Demographics!$B$11:$B$6301,"Pottery Class",Demographics!$H$11:$H$6301)
C17C17=IF(COUNTIF(Demographics!$B$11:$B$6301,"Women's Support")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Women's Support"))
Q17Q17=SUMIF(Demographics!$B$11:$B$6301,"Women's Support",Demographics!$H$11:$H$6301)
C18C18=IF(COUNTIF(Demographics!$B$11:$B$6301,"Other")=0,"",COUNTIF(Demographics!$B$11:$B$6301,"Other"))
Q18Q18=SUMIF(Demographics!$B$11:$B$6301,"Other",Demographics!$H$11:$H$6301)
S9:S21S9=IFERROR(INDEX(Demographics!$G$11:$G$6301,MATCH(0,INDEX(COUNTIF($S$8:S8,Demographics!$G$11:$G$6301),,),)),"")
C21C21=IF(COUNTIF(Demographics!$C$11:$C$6301,"Female")=0,"",COUNTIF(Demographics!$C$11:$C$6301,"Female"))
C22C22=IF(COUNTIF(Demographics!$C$11:$C$6301,"Male")=0,"",COUNTIF(Demographics!$C$11:$C$6301,"Male"))
C23C23=IF(COUNTIF(Demographics!$C$11:$C$6301,"Transgender")=0,"",COUNTIF(Demographics!$C$11:$C$6301,"Transgender"))
C24C24=SUM($C$21:$C$23)
C27C27=IF(COUNTIF(Demographics!$D$11:$D$6301,"18 - 25")=0,"",COUNTIF(Demographics!$D$11:$D$6301,"18 - 25"))
C28C28=IF(COUNTIF(Demographics!$D$11:$D$6301,"26 - 35")=0,"",COUNTIF(Demographics!$D$11:$D$6301,"26 - 35"))
C29C29=IF(COUNTIF(Demographics!$D$11:$D$6301,"36 - 45")=0,"",COUNTIF(Demographics!$D$11:$D$6301,"36 - 45"))
C30C30=IF(COUNTIF(Demographics!$D$11:$D$6301,"46 and over")=0,"",COUNTIF(Demographics!$D$11:$D$6301,"46 and over"))
C33C33=IF(COUNTIF(Demographics!$E$11:$E$6301,"HF")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"HF"))
C34C34=IF(COUNTIF(Demographics!$E$11:$E$6301,"PRS")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"PRS"))
C35C35=IF(COUNTIF(Demographics!$E$11:$E$6301,"Rough sleeping")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"Rough sleeping"))
C36C36=IF(COUNTIF(Demographics!$E$11:$E$6301,"Social")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"Social"))
C37C37=IF(COUNTIF(Demographics!$E$11:$E$6301,"TA")=0,"",COUNTIF(Demographics!$E$11:$E$6301,"TA"))
C40C40=IF(COUNTIF(Demographics!$F$11:$F$6301,"Centre only")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"Centre only"))
C41C41=IF(COUNTIF(Demographics!$F$11:$F$6301,"Homeworks")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"Homeworks"))
C42C42=IF(COUNTIF(Demographics!$F$11:$F$6301,"RRP")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"RRP"))
C43C43=IF(COUNTIF(Demographics!$F$11:$F$6301,"RSI")=0,"",COUNTIF(Demographics!$F$11:$F$6301,"RSI"))
C44C44=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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
@SaraWitch
I suspect not in your preferred location but maybe something like....

Book1
ABC
7ActivityTotal Number of Sessions Held
8Unique Dated
91Bowling2
103Breakfast Club5
110Cinema 
122Cooking5
130Exercise Class 
140Games 
150Gardening 
160Pottery Class 
170Women's Support 
180Other 
19
Summary
Cell Formulas
RangeFormula
A9:A18A9{=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.
 
Last edited:

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Amazing! Works exactly as I want it to (and I just put your formula in my Summary sheet column C - perfect!). :):)

Thank you, Snakehips!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,485
Members
410,685
Latest member
chandraganji
Top