Sumproduct #Value Error

AYouQueTai

New Member
Joined
Sep 14, 2019
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
Can anyone figure out why I am getting a #Value error.
I've tried entering the following formula's in cell E16
=SUMPRODUCT((SeasonSeries[Visitor]=A8)*(SeasonSeries[Vgoal]))
=SUMPRODUCT((SeasonSeries[Visitor]=A8)*(--(SeasonSeries[Vgoal])))
I've checked the formatting in Season Series table. xl2bb says the worksheet is too big to post so i am copying the 2 essential parts of it.

NHL 2021.xlsx
ABCDEF
11TeamStatMatchupGPG/GAGPG
12VEGOverallOffense14423.00
13COLOverallDefense13262.00
14VEGLast 6Offense6152.50
15COLLast 6Defense681.33
16VEGSeriesOffense2#VALUE!0.00
17COLSeriesDefense20.00
Matchup NEW
Cell Formulas
RangeFormula
E12E12=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=3)))
E13E13=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GA Team]=$E$8)*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GA Team]=$E$8)*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$F$2)*(Goals[Date]<$B$2)*(Goals[GA Team]=$E$8)*(Goals[Period]=3)))
E14E14=SUM(SUMPRODUCT((VLast6[Visitor]=$A8)*(VLast6[Vgoal])),SUMPRODUCT((VLast6[Home]=$A8)*(VLast6[Hgoal])))
E15E15=SUM(SUMPRODUCT((HLast6[Visitor]=$E8)*(HLast6[Hgoal])),SUMPRODUCT((HLast6[Home]=$E8)*(HLast6[Vgoal])))
E16E16=SUMPRODUCT((SeasonSeries[Visitor]=A8)*(--(SeasonSeries[Vgoal])))
A12,A16,A14A12=VLOOKUP($A$8,Teams[#All],3,FALSE)
A13,A17,A15A13=VLOOKUP($E$8,Teams[#All],3,FALSE)
D12D12=SUM(SUMPRODUCT((Results[Date]>=Dropdowns!$F$2)*(Results[Date]<$B$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)),SUMPRODUCT((Results[Date]>=Dropdowns!$F$2)*(Results[Date]<$B$2)*(Results[Status]="Completed")*(Results[Home]=$A$8)))
D13D13=SUM(SUMPRODUCT((Results[Date]>=Dropdowns!$F$2)*(Results[Date]<$B$2)*(Results[Status]="Completed")*(Results[Visitor]=$E$8)),SUMPRODUCT((Results[Date]>=Dropdowns!$F$2)*(Results[Date]<$B$2)*(Results[Status]="Completed")*(Results[Home]=$E$8)))
D14D14=SUM(SUMPRODUCT((VLast6[Date]>=Dropdowns!$F$2)*(VLast6[Date]<$B$2)*(VLast6[Visitor]=$A$8)),SUMPRODUCT((VLast6[Date]>=Dropdowns!$F$2)*(VLast6[Date]<$B$2)*(VLast6[Home]=$A$8)))
D15D15=SUM(SUMPRODUCT((HLast6[Date]>=Dropdowns!$F$2)*(HLast6[Date]<$B$2)*(HLast6[Visitor]=$E$8)),SUMPRODUCT((HLast6[Date]>=Dropdowns!$F$2)*(HLast6[Date]<$B$2)*(HLast6[Home]=$E$8)))
D16D16=SUM(SUMPRODUCT((SeasonSeries[Date]>=Dropdowns!$F$2)*(SeasonSeries[Date]<$B$2)*(SeasonSeries[Visitor]=$A$8)),SUMPRODUCT((SeasonSeries[Date]>=Dropdowns!$F$2)*(SeasonSeries[Date]<$B$2)*(SeasonSeries[Home]=$A$8)))
D17D17=SUM(SUMPRODUCT((SeasonSeries[Date]>=Dropdowns!$F$2)*(SeasonSeries[Date]<$B$2)*(SeasonSeries[Visitor]=$E$8)),SUMPRODUCT((SeasonSeries[Date]>=Dropdowns!$F$2)*(SeasonSeries[Date]<$B$2)*(SeasonSeries[Home]=$E$8)))
F12:F15F12=E12/D12
F16:F17F16=IF(ISERROR(E16/D16),0,(E16/D16))



Cell Formulas
RangeFormula
A47:A54A47=IFERROR(INDEX(Results[GameID],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
B47:B54B47=IFERROR(INDEX(Results[Date],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
C47:C54C47=IFERROR(INDEX(Results[Visitor],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
D47:D54D47=IFERROR(INDEX(Results[VGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
E47:E54E47=IFERROR(INDEX(Results[Home],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
F47:F54F47=IFERROR(INDEX(Results[HGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
A55A55=IFERROR(INDEX(Results[GameID],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
B55B55=IFERROR(INDEX(Results[Date],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
C55C55=IFERROR(INDEX(Results[Visitor],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
D55D55=IFERROR(INDEX(Results[VGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
E55E55=IFERROR(INDEX(Results[Home],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
F55F55=IFERROR(INDEX(Results[HGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
A56A56=IFERROR(INDEX(Results[GameID],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
B56B56=IFERROR(INDEX(Results[Date],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
C56C56=IFERROR(INDEX(Results[Visitor],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
D56D56=IFERROR(INDEX(Results[VGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
E56E56=IFERROR(INDEX(Results[Home],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
F56F56=IFERROR(INDEX(Results[HGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A9))),"")
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
Could you post a limited range of the Season Series table including the headings, and a few cells around and including A8?
 

AYouQueTai

New Member
Joined
Sep 14, 2019
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
Hi KRice,

Here is A8 and the cells around it.

NHL 2021.xlsx
ABCDEFGH
7
8VegasColorado
9
Matchup NEW
Cell Formulas
RangeFormula
A8A8=VLOOKUP(B1,Results[#All],6,FALSE)
E8E8=VLOOKUP(B1,Results[#All],7,FALSE)


But I'm not sure what you mean by limited range of the season series, sorry. Like this?

NHL 2021.xlsx
ABCDEF
45Season Series
46GameIDDateVisitorVgoalHomeHgoal
47020243Feb-14Colorado0Vegas1
Matchup NEW
Cell Formulas
RangeFormula
A47A47=IFERROR(INDEX(Results[GameID],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
B47B47=IFERROR(INDEX(Results[Date],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
C47C47=IFERROR(INDEX(Results[Visitor],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
D47D47=IFERROR(INDEX(Results[VGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
E47E47=IFERROR(INDEX(Results[Home],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
F47F47=IFERROR(INDEX(Results[HGoals],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A2))),"")
 

AYouQueTai

New Member
Joined
Sep 14, 2019
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
oops, here you go krice. here is the range that season series looks at.

Cell Formulas
RangeFormula
B2:B5B2=VLOOKUP([@GameID],Schedule[#All],2,FALSE)
C2:C5C2=VLOOKUP([@GameID],Schedule[#All],3,FALSE)
D2:D5D2=VLOOKUP([@GameID],Schedule[#All],4,FALSE)
E2:E5E2=IF(WEEKDAY([@Date],1)=1,"Sunday",IF(WEEKDAY([@Date],1)=2,"Monday",IF(WEEKDAY([@Date],1)=3,"Tuesday",IF(WEEKDAY([@Date],1)=4,"Wednesday",IF(WEEKDAY([@Date],1)=5,"Thursday",IF(WEEKDAY([@Date],1)=6,"Friday",IF(WEEKDAY([@Date],1)=7,"Saturday")))))))
F2:F5F2=VLOOKUP([@GameID],Schedule[#All],5,FALSE)
G2:G5G2=VLOOKUP([@GameID],Schedule[#All],6,FALSE)
H2:H5H2=VLOOKUP([@GameID],Schedule[#All],7,FALSE)
I2:I5I2=SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=1)*(Goals[GF Team]=[@Visitor]))
J2:J5J2=SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=2)*(Goals[GF Team]=[@Visitor]))
K2:K5K2=SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=3)*(Goals[GF Team]=[@Visitor]))
L2:L5L2=SUM(SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=1)*(Goals[GF Team]=[@Visitor])),SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=2)*(Goals[GF Team]=[@Visitor])),SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=3)*(Goals[GF Team]=[@Visitor])))
M2:M5M2=IF([@VGoals]=[@HGoals],"Tie",IF([@VGoals]>[@HGoals],"Win","Loss"))
N2:N5N2=SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=1)*(Goals[GF Team]=[@Home]))
O2:O5O2=SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=2)*(Goals[GF Team]=[@Home]))
P2:P5P2=SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=3)*(Goals[GF Team]=[@Home]))
Q2:Q5Q2=SUM(SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=1)*(Goals[GF Team]=[@Home])),SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=2)*(Goals[GF Team]=[@Home])),SUMPRODUCT((Goals[Game ID]=[@GameID])*(Goals[Period]=3)*(Goals[GF Team]=[@Home])))
R2:R5R2=IF([@HGoals]=[@VGoals],"Tie",IF([@HGoals]>[@VGoals],"Win","Loss"))
Cells with Data Validation
CellAllowCriteria
B2:B5List=Dropdowns!$A$2:$A$3
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Sorry...my fault. You had already included a portion of the Season Series table that I was interested in seeing. It's difficult to know for certain without having the named tables with structured references. My first thought is that you might be including the column headings in the range references, which could be responsible for the #VALUE! error. Try entering a formula without the structured reference to see if that works first:
=SUMPRODUCT($C$47:$C$67=$A$8)*($D$47:$D$67))
Extend the range to whatever is appropriate to obtain a match for Visitor=Vegas. If that works, then redo the structured formula to eliminate the header.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
I had a typo...
=SUMPRODUCT(($C$47:$C$67=$A$8)*($D$47:$D$67))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,258
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

As you have formulae that return "" try
Excel Formula:
=SUMPRODUCT(--(SeasonSeries[Visitor]=A8),SeasonSeries[Vgoal])
 
Solution

AYouQueTai

New Member
Joined
Sep 14, 2019
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
ok I tried with a part of the range that returned actual values and it worked fine.
The problem is the #Num! error that the formula in the season series evaluates to but then turns it into a blank with the iferror ""

=IFERROR(INDEX(Results[Visitor],AGGREGATE(15,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((Results[Visitor]=$A$8)+(Schedule[Visitor]=$E$8))/(Results[Date]<$B$2)/((Schedule[Home]=$A$8)+(Schedule[Home]=$E$8))/(Schedule[Status]="Completed"),ROWS(A$2:A3))),"")

I just tried replacing the "" with a 0 and it fixed the value error i was having but now it looks bad and annoys me lol.
Is there a way around this?
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
Have a look at the recommendation from @Fluff...that works in a sample worksheet I made.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,027
Messages
5,622,284
Members
415,890
Latest member
macak333

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top