SUMProduct formula error

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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