SUMProduct formula error

T

Taff

Board Regular
Joined
Mar 31, 2004
Messages
151
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi all,

any reason why i would be getting a #Value error using the formula:

=SUMPRODUCT(--(Season=$LU$2),--(home=$LV5),--(FTHomegoals+FTAwaygoals>MV3))

Season Lu2 is referencing the range of the data i wont to use
Home = LV5 is pointing to the particular team & where it would be listed
FTHomegoals is a column with all home goals
FTawaygoals is a column with all away goals
MV3 is a figure i am using as a reference point.

Ideally i want to find out home many times the home team (LV5) has been involved in matches during the season (LU2) and the total goals (FThomegoals+FTAwaygoals) have been more that MV3 (an arbitrator figure i enter manually)

As always appreciate any guidance

Taff
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
I have no idea what you're trying to achieve, but it looks like this should be the third argument in function SUMPRODUCT:
--((FTHomegoals+FTAwaygoals)>MV3)​
 
Upvote 0
Another thing to check is make sure your ranges are all the same length (Season, home, etc.).
 
Upvote 0
It would help if you could post a small sample of your data and the outcome you expect.
 
Upvote 0
Did you check to make sure the ranges are all the same size, as per AhoyNC?

Also, check to make sure that FTHomegoals and FTAwaygoals do not contain one or more cells that contain text values.
 
Last edited:
Upvote 0
All ranges are same size, confirmed no numbers are text. Sample date below: trying to confirm how many times a team scores a total >2.5 goals in the given season
<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (https://www.asap-utilities.com) -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF">
<tr align="center" valign="middle">
<td bgcolor="#FFFF00" width="63" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>2018-19</b></font></td>
<td bgcolor="#FFFFFF" width="93" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#DBDBDB" width="63" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#006600"><b>Rank</b></font></td>
<td bgcolor="#DBDBDB" width="93" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#006600"><b>Team</b></font></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#FF0000">2.5</font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#63BE7B" width="63" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#006600"><b>3</b></font></td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Arsenal</font></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#FF0000">1</font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#DAEEE1" width="63" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#006600"><b>10</b></font></td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Bournemouth</font></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#FF0000">0</font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#F8696B" width="63" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#006600"><b>14</b></font></td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Brighton</font></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#FF0000">0</font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#F8696B" width="63" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#006600"><b>14</b></font></td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Burnley</font></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap"><font face="Tahoma" size="2" color="#FF0000">0</font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="93" height="25" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="78" height="25" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="93" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Season</b></font></td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>HomeTeam</b></font></td>
<td bgcolor="#FFFFFF" width="78" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>AwayTeam</b></font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>FTHomegoals</b></font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>FTAwaygoals</b></font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2011-12</font></td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Arsenal</font></td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Wolves</font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2</font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2017-18</font></td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Bournemouth</font></td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Aston Villa</font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">0</font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">0</font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2018-19</font></td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Brighton</font></td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Sunderland</font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2017-18</font></td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Burnley</font></td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Arsenal</font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">0</font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">0</font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2018-19</font></td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Arsenal</font></td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Bolton</font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">0</font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">4</font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2018-19</font></td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Arsenal</font></td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Norwich</font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2018-19</font></td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Burnley</font></td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Chelsea</font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">0</font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">0</font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2017-18</font></td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">West Brom</font></td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Man United</font></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2</font></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2018-19</font></td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Brighton</font></td>
<td bgcolor="#FFFFFF" width="78" height="26" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Swansea</font></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">4</font></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">0</font></td>
</tr>
</table>

formula being used in c3=SUMPRODUCT(--($A$10:$A$18=$A$1),--($B10:$B18=$B3),--($D$10:$D$18+$E$10:$E$18>$C$2)) previously i had a #Value error, now an incorrect count....
 
Last edited:
Upvote 0
You haven't locked the ranges for $B$10:$B18
 
Upvote 0
noted, but when locked still incorrect answer
 
Upvote 0
I get


Book1
ABCDE
12018-19
2RankTeam2.5
33Arsenal1
410Bournemouth0
514Brighton1
614Burnley0
7
8
9SeasonHomeTeamAwayTeamFTHomegoalsFTAwaygoals
102011-12ArsenalWolves12
112017-18BournemouthAston Villa00
122018-19BrightonSunderland11
132017-18BurnleyArsenal00
142018-19ArsenalBolton04
152018-19ArsenalNorwich11
162018-19BurnleyChelsea00
172017-18West BromMan United12
182018-19BrightonSwansea40
Customers
Cell Formulas
RangeFormula
C3=SUMPRODUCT(--($A$10:$A$18=$A$1),--($B$10:$B$18=$B3),--($D$10:$D$18+$E$10:$E$18>$C$2))


Which is correct.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F
A
  • Question Question
Fetch heads up stats between 2 teams
Replies
2
Views
275
AYouQueTai
A
H
  • Question Question
'How to' question about a Dashboard
Replies
5
Views
652
etaf
etaf
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
1K
USNA91
U
S
  • Question Question
analyze and export based on multiple criteria
Replies
0
Views
526
slash32487
S

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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