SUMPRODUCT from a range of cells, one of which contains a formula

[SaraWitch]

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):

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				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
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!

 

[Peter_SSs]

Remove the quotes from around the 1 in this formula on Demographics

=IF($B12>0,1,"")

 

[Fluff]

The values in col H are text not numbers, remove the quotes from the "1".

Also do you mean it has to work in Excel 2007, or 2010?

 

[SaraWitch]

Perfect; sometimes I can't see the wood for the trees! Really appreciate both your help; thank you!

(And, sorry, Fluff; Excel 2010!)

 

[Peter_SSs]

You're welcome. Glad we could help.

 

[Fluff]

Ok, 2010 is fine, but it wouldn't have worked in 2007 as that didn't have the Mode.Mult function.