SUMProduct formula error

Taff

Board Regular
Joined
Mar 31, 2004
Messages
138
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,255
Office Version
365
Platform
Windows
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)
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,538
Office Version
365
Platform
Windows
Another thing to check is make sure your ranges are all the same length (Season, home, etc.).
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,538
Office Version
365
Platform
Windows
It would help if you could post a small sample of your data and the outcome you expect.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,208
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:

Taff

Board Regular
Joined
Mar 31, 2004
Messages
138
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
You haven't locked the ranges for $B$10:$B18
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
I get

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">2018-19</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #006600;background-color: #DBDBDB;;">Rank</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #006600;background-color: #DBDBDB;;">Team</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FF0000;background-color: #FFFFFF;;">2.5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #006600;background-color: #63BE7B;;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Arsenal</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #006600;background-color: #DAEEE1;;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Bournemouth</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #006600;background-color: #F8696B;;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Brighton</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #006600;background-color: #F8696B;;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Burnley</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Season</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">HomeTeam</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">AwayTeam</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">FTHomegoals</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">FTAwaygoals</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2011-12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Arsenal</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Wolves</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2017-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Bournemouth</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Aston Villa</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2018-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Brighton</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Sunderland</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2017-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Burnley</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Arsenal</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2018-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Arsenal</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Bolton</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2018-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Arsenal</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Norwich</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2018-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Burnley</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Chelsea</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2017-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">West Brom</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Man United</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2018-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Brighton</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Swansea</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Customers</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$10:$A$18=$A$1</font>),--(<font color="Red">$B$10:$B$18=$B3</font>),--(<font color="Red">$D$10:$D$18+$E$10:$E$18>$C$2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Which is correct.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,464
Messages
5,487,032
Members
407,576
Latest member
aapnarritesh

This Week's Hot Topics

Top