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

#### SaraWitch

##### Board Regular
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...

### 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
Remove the quotes from around the 1 in this formula on Demographics

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

#### Fluff

##### MrExcel MVP, Moderator
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
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
You're welcome. Glad we could help.

#### Fluff

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

Replies
2
Views
73
Replies
18
Views
226
Replies
3
Views
209
Replies
3
Views
192
Replies
1
Views
113