SUMProduct formula error

Taff · Jul 20, 2019

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

DRSteele · Jul 20, 2019

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 · Jul 20, 2019

Another thing to check is make sure your ranges are all the same length (Season, home, etc.).

Taff · Jul 20, 2019

Thanks but, Nope still getting nowhere!!

AhoyNC · Jul 20, 2019

It would help if you could post a small sample of your data and the outcome you expect.

Domenic · Jul 20, 2019

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.

Taff · Jul 21, 2019

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


<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">2018-19</td>
<td bgcolor="#FFFFFF" width="93" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#DBDBDB" width="63" height="26" align="center" valign="middle" style="white-space: nowrap">Rank</td>
<td bgcolor="#DBDBDB" width="93" height="26" align="center" valign="middle" style="white-space: nowrap">Team</td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap">2.5</td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#63BE7B" width="63" height="26" align="center" valign="middle" style="white-space: nowrap">3</td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap">Arsenal</td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap">1</td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#DAEEE1" width="63" height="26" align="center" valign="middle" style="white-space: nowrap">10</td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap">Bournemouth</td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap">0</td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#F8696B" width="63" height="26" align="center" valign="middle" style="white-space: nowrap">14</td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap">Brighton</td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap">0</td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#F8696B" width="63" height="26" align="center" valign="middle" style="white-space: nowrap">14</td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap">Burnley</td>
<td bgcolor="#FFFFFF" width="78" height="26" align="center" valign="middle" style="white-space: nowrap">0</td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="93" height="25" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="78" height="25" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="89" height="25" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="86" height="25" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="93" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="78" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="89" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
<td bgcolor="#FFFFFF" width="86" height="26" align="right" valign="bottom" style="white-space: nowrap"></td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="26" valign="bottom" style="white-space: nowrap">Season</td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap">HomeTeam</td>
<td bgcolor="#FFFFFF" width="78" height="26" valign="bottom" style="white-space: nowrap">AwayTeam</td>
<td bgcolor="#FFFFFF" width="89" height="26" align="center" valign="bottom" style="white-space: nowrap">FTHomegoals</td>
<td bgcolor="#FFFFFF" width="86" height="26" align="center" valign="bottom" style="white-space: nowrap">FTAwaygoals</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">2011-12</td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap">Arsenal</td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap">Wolves</td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap">1</td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap">2</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">2017-18</td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap">Bournemouth</td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap">Aston Villa</td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap">0</td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap">0</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">2018-19</td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap">Brighton</td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap">Sunderland</td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap">1</td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap">1</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">2017-18</td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap">Burnley</td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap">Arsenal</td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap">0</td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap">0</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">2018-19</td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap">Arsenal</td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap">Bolton</td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap">0</td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap">4</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">2018-19</td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap">Arsenal</td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap">Norwich</td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap">1</td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap">1</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">2018-19</td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap">Burnley</td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap">Chelsea</td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap">0</td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap">0</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">2017-18</td>
<td bgcolor="#FFFFFF" width="93" height="25" valign="bottom" style="white-space: nowrap">West Brom</td>
<td bgcolor="#FFFFFF" width="78" height="25" valign="bottom" style="white-space: nowrap">Man United</td>
<td bgcolor="#FFFFFF" width="89" height="25" align="center" valign="bottom" style="white-space: nowrap">1</td>
<td bgcolor="#FFFFFF" width="86" height="25" align="center" valign="bottom" style="white-space: nowrap">2</td>
</tr>
<tr align="center" valign="middle">
<td bgcolor="#FFFFFF" width="63" height="26" valign="bottom" style="white-space: nowrap">2018-19</td>
<td bgcolor="#FFFFFF" width="93" height="26" valign="bottom" style="white-space: nowrap">Brighton</td>
<td bgcolor="#FFFFFF" width="78" height="26" valign="bottom" style="white-space: nowrap">Swansea</td>
<td bgcolor="#FFFFFF" width="89" height="26" align="center" valign="bottom" style="white-space: nowrap">4</td>
<td bgcolor="#FFFFFF" width="86" height="26" align="center" valign="bottom" style="white-space: nowrap">0</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....

Fluff · Jul 21, 2019

You haven't locked the ranges for $B$10:$B18

Taff · Jul 21, 2019

noted, but when locked still incorrect answer

Fluff · Jul 21, 2019

I get

Book1

A

B

C

D

E

1

2018-19

2

Rank

Team

2.5

3

Arsenal

1

4

10

Bournemouth

0

5

14

Brighton

1

6

14

Burnley

0

7

8

9

Season

HomeTeam

AwayTeam

FTHomegoals

FTAwaygoals

10

2011-12

Arsenal

Wolves

1

2

11

2017-18

Bournemouth

Aston Villa

0

12

2018-19

Brighton

Sunderland

1

13

2017-18

Burnley

Arsenal

0

14

2018-19

Arsenal

Bolton

0

4

15

2018-19

Arsenal

Norwich

1

16

2018-19

Burnley

Chelsea

0

17

2017-18

West Brom

Man United

1

2

18

2018-19

Brighton

Swansea

4

0

Customers

Cell Formulas
Range	Formula
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.

SUMProduct formula error

Taff

Board Regular

Excel Facts

DRSteele

Well-known Member

AhoyNC

Well-known Member

Taff

Board Regular

AhoyNC

Well-known Member

Domenic

MrExcel MVP

Taff

Board Regular

Fluff

MrExcel MVP, Moderator

Taff

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

SUMProduct formula error

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Well-known Member

MrExcel MVP

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock