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))),"")
 
Sort by date Sort by votes
Glad we could help & thanks for the feedback.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Thank's @Fluff. Do you have any insight into why a comma must separate the two arguments in SUMPRODUCT? Your suggestion with a comma works, while this version that explicitly multiplies the two arrays does not:
Excel Formula: 
=SUMPRODUCT(--(SeasonSeries[Visitor]=A8)*SeasonSeries[Vgoal])
Wouldn't these be equivalent...apparently they are not?
 
Upvote 0
They are basically the same, it's just how its handled. Because "" is text if you multiply it by something you get #VALUE! as you cannot multiply text. However using the comma Sumproduct simply sums the 2nd array where the 1st array is true, so nothing is getting multiplied.
 
Upvote 0
Very helpful! Thanks for the explanation and the solution above.
 
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
122
AYouQueTai
A
A
  • Solved
Countifs? Count events in a range if something doesn't happen in another range
Replies
2
Views
158
AYouQueTai
A
JenniferMurphy
  • Solved
How to calculate a moving count?
2
Replies
15
Views
171
JenniferMurphy
JenniferMurphy
M
  • Question
Recursive LAMBDA for progressive SMALL calculations
Replies
1
Views
73
macfuller
M
JenniferMurphy
  • Question
How to synchronize two tables?
Replies
0
Views
107
JenniferMurphy
JenniferMurphy

Forum statistics

Threads
1,214,971
Messages
6,122,525
Members
449,088
Latest member
RandomExceller01

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