SUMProduct formula error
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: SUMProduct formula error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2004
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMProduct formula error

    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
    "Every day when I wake up I thank the Lord I'm Welsh!"

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SUMProduct formula error

    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)
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,220
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SUMProduct formula error

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

  4. #4
    Board Regular
    Join Date
    Mar 2004
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMProduct formula error

    Thanks but, Nope still getting nowhere!!
    "Every day when I wake up I thank the Lord I'm Welsh!"

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,220
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SUMProduct formula error

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

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,791
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    5 Thread(s)

    Default Re: SUMProduct formula error

    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 by Domenic; Jul 20th, 2019 at 09:50 PM.

  7. #7
    Board Regular
    Join Date
    Mar 2004
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMProduct formula error

    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
    2018-19
    Rank Team 2.5
    3 Arsenal 1
    10 Bournemouth 0
    14 Brighton 0
    14 Burnley 0
    Season HomeTeam AwayTeam FTHomegoals FTAwaygoals
    2011-12 Arsenal Wolves 1 2
    2017-18 Bournemouth Aston Villa 0 0
    2018-19 Brighton Sunderland 1 1
    2017-18 Burnley Arsenal 0 0
    2018-19 Arsenal Bolton 0 4
    2018-19 Arsenal Norwich 1 1
    2018-19 Burnley Chelsea 0 0
    2017-18 West Brom Man United 1 2
    2018-19 Brighton Swansea 4 0
    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 by Taff; Jul 21st, 2019 at 09:59 AM.
    "Every day when I wake up I thank the Lord I'm Welsh!"

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,491
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: SUMProduct formula error

    You haven't locked the ranges for $B$10:$B18
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Mar 2004
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Re: SUMProduct formula error

    noted, but when locked still incorrect answer
    "Every day when I wake up I thank the Lord I'm Welsh!"

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,491
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: SUMProduct formula error

    I get

    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



    Worksheet Formulas
    CellFormula
    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •