SaraWitch
Board Regular
 Joined
 Sep 29, 2015
 Messages
 52
 Office Version

 365
 2010
 Platform

 Windows
Good morning everyone.
I wonder if someone can help with a SUMPRODUCT formula that works perfectly if I manually enter data in the other cells. However, as soon as I add a simple IF formula to one of those cells, it stops working.
SUMPRODUCT formula is in a Summary Sheet (D9:P18):
And count data on a separate sheet ('Demographics'), with the IF formula in column H12:H22 (row 11 has no formula to demonstrate SUMPRODUCT works without formula!):
I need this to work for both Excel 2007 and Office 365...
Any advice would be gratefully received, thank you!
I wonder if someone can help with a SUMPRODUCT formula that works perfectly if I manually enter data in the other cells. However, as soon as I add a simple IF formula to one of those cells, it stops working.
SUMPRODUCT formula is in a Summary Sheet (D9:P18):
RSI Activities WEST.xlsm  

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  
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  
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  
9  Bowling  2  0  0  0  0  0  0  0  0  0  0  0  0  0  
10  Breakfast Club  5  1  0  0  0  0  0  0  0  0  0  0  0  1  
11  Cinema  0  0  0  0  0  0  0  0  0  0  0  0  0  
12  Cooking  5  0  0  0  0  0  0  0  0  0  0  0  0  0  
13  Exercise Class  0  0  0  0  0  0  0  0  0  0  0  0  0  
14  Games  0  0  0  0  0  0  0  0  0  0  0  0  0  
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  
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!$B$11:$B$6301,"Bowling")=0,"",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) 
And count data on a separate sheet ('Demographics'), with the IF formula in column H12:H22 (row 11 has no formula to demonstrate SUMPRODUCT works without formula!):
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  1  
15  Breakfast Club  Female  18  25  Rough sleeping  Centre only  1  
16  Cooking  Female  18  25  Rough sleeping  Centre only  01/06/2020  1  
17  Cooking  Female  26  35  HF  Homeworks  1  
18  Cooking  Female  18  25  HF  Homeworks  01/07/2020  1  
19  Cooking  Female  26  35  HF  Homeworks  1  
20  Cooking  Female  26  35  HF  Homeworks  1  
21  Bowling  Female  26  35  HF  Homeworks  1  
22  Bowling  Male  26  35  HF  Homeworks  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) 
H12:H22  H12  =IF($B12>0,"1","") 
Press CTRL+SHIFT+ENTER to enter array formulas. 
I need this to work for both Excel 2007 and Office 365...
Any advice would be gratefully received, thank you!