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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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,541
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,541
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,211
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,903
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,903
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,783
Messages
5,488,854
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top