SaraWitch
New Member
 Joined
 Sep 29, 2015
 Messages
 49
 Office Version

 365
 2010
 Platform

 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:
This data is captured on a Summary sheet, which I've played around with formulas, as you will see!
Formula needs to work in Excel 2010 and Office 365.
Any help would be appreciated!
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  Apr20  May20  Jun20  Jul20  Aug20  Sep20  Oct20  Nov20  Dec20  Jan21  Feb21  Mar21  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!