Sumproduct #Value Error

A

AYouQueTai

New Member
Joined
Sep 14, 2019
Messages
34
Office Version
  1. 365
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))



NHL 2021.xlsx
ABCDEF
45Season Series
46GameIDDateVisitorVgoalHomeHgoal
47020243Feb-14Colorado0Vegas1
48020259Feb-16Colorado3Vegas2
49      
50      
51      
52      
53      
54      
55      
56      
Matchup NEW
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))),"")
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Could you post a limited range of the Season Series table including the headings, and a few cells around and including A8?
 
Upvote 0
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))),"")
 
Upvote 0
oops, here you go krice. here is the range that season series looks at.

NHL 2021.xlsx
ABCDEFGHIJKLMNOPQR
1GameIDStatusDateTimeDOWVisitorHomeDivisionVP1GFVP2GFVP3GFVGoalsVResHP1GFHP1GF2HP1GF3HGoalsHRes
2020001CompletedJan-135:30 PMWednesdayPittsburghPhiladelphiaEast1113Loss2136Win
3020002CompletedJan-138:00 PMWednesdayChicagoTampa BayCentral0011Loss3115Win
4020003CompletedJan-137:00 PMWednesdayMontrealTorontoNorth2114Tie1214Tie
5020004CompletedJan-1310:00 PMWednesdayVancouverEdmontonNorth1135Win0123Loss
Results
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
 
Upvote 0
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.
 
Upvote 0
I had a typo...
=SUMPRODUCT(($C$47:$C$67=$A$8)*($D$47:$D$67))
 
Upvote 0
As you have formulae that return "" try
Excel Formula: 
=SUMPRODUCT(--(SeasonSeries[Visitor]=A8),SeasonSeries[Vgoal])
 
Upvote 0
Solution
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?
 
Upvote 0
Have a look at the recommendation from @Fluff...that works in a sample worksheet I made.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Fetch heads up stats between 2 teams
Replies
2
Views
116
AYouQueTai
A
A
  • Solved
Countifs? Count events in a range if something doesn't happen in another range
Replies
2
Views
150
AYouQueTai
A
JenniferMurphy
  • Question
How to synchronize two tables?
Replies
0
Views
106
JenniferMurphy
JenniferMurphy
J
  • Question
sumif function with delayed application
Replies
5
Views
115
jbrown021286
J
J
  • Solved
adding cells based on day of the week
Replies
1
Views
75
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,214,784
Messages
6,121,536
Members
449,037
Latest member
tmmotairi

Share this page

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
Back
Top