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

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
52
Office Version
  1. 365
  2. 2010
Platform
  1. 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):
RSI Activities WEST.xlsm
ABCDEFGHIJKLMNOPQ
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 
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-21Total
9Bowling20000000000000
10Breakfast Club51000000000001
11Cinema 0000000000000
12Cooking50000000000000
13Exercise Class 0000000000000
14Games 0000000000000
15Gardening 0000000000000
16Pottery Class 0000000000000
17Women's Support 0000000000000
18Other 0000000000000
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!$B$11:$B$6301,"Bowling")=0,"",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)


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
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 1
15Breakfast ClubFemale 18 - 25 Rough sleeping Centre only 1
16CookingFemale 18 - 25 Rough sleeping Centre only 01/06/20201
17CookingFemale 26 - 35 HF Homeworks 1
18CookingFemale 18 - 25 HF Homeworks 01/07/20201
19CookingFemale 26 - 35 HF Homeworks 1
20CookingFemale 26 - 35 HF Homeworks 1
21BowlingFemale 26 - 35 HF Homeworks 1
22BowlingMale 26 - 35 HF Homeworks 1
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)
H12:H22H12=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!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,526
Office Version
  1. 365
Platform
  1. Windows
Remove the quotes from around the 1 in this formula on Demographics

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 29, 2015
Messages
52
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,526
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Ok, 2010 is fine, but it wouldn't have worked in 2007 as that didn't have the Mode.Mult function.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,221
Messages
5,576,816
Members
412,748
Latest member
MikeyP14
Top