+ Reply to Thread
Results 1 to 6 of 6

Sumproduct and summing last occurrences of criteria

  1. #1
    Registered User
    Join Date
    05-14-2012
    Location
    Somewhere, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Sumproduct and summing last occurrences of criteria

    Hi everyone

    I’m just beginning to get the hang of some functions in excel and I haven’t had to resort to asking this board for help…

    Until now. Here’s what i’m trying to do.

    In the file you can see i’m attempting to set up a dynamic match rating football system. Each team takes points off one another with all teams starting at 1000 to give them a relative strength. Each side contributes a % of their total spoils are distributed according to whether the match is a home/away win or draw.

    My problem is that by using SUMPRODUCT, it is summing all the occurrences of the criteria being met (if you drag I14:M14 down you'll see what I mean; the average for all 24 teams should be approx 1000), whereas I’m only looking for the last occurrence in each case (the last time the criteria of ‘team name’ was met. How do I have excel only count the last event of met criteria?

    I’d also like to know how to sum the last six occurrences for a separate task.

    Any advice/help would be appreciated. Hope my question makes sense.
    Attached Files Attached Files

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Sumproduct and summing last occurrences of criteria

    I'm not sure exactly what you are trying to do...what should your formula evaluate to in cell I14.

    I can tell you, if you are looking for the last occurence you should probably look at something like the following array formula:
    {=MAX(IF(B2:C13=B14,ROW(B2:C13),0))} -- you don't enter the curly braces, you type the formula and confirm with cntrl+shift+enter (instead of just enter). This formula would return the number of the row in which the condition was met last...but i still don't see what you want to do with it once you have that.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    05-14-2012
    Location
    Somewhere, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Sumproduct and summing last occurrences of criteria

    Thanks General, i'll try to clarify.

    In cell I14, I want the rateform for team in cell B14. That rateform will come from somewhere in the array L2:Mxxx and it should give me the last occurence from the array L2:Mxxx (only up to M13 in the spreadsheet uploaded) of that team in cell B14 (their last game may have been home or away so the rateform value may be in L or M). This will give me the rateform pts for that team and I am only looking for the last rateform, ie team in cell B14's rrateform after their last match played.

    Once that number is in cell I14, I want to do the same process for the opponent in that match in cell J14 (which is in cell C14, 'Crystal Palace' in this case) and I want to repeat the process so that the two team will each have a respective match rating.

    You can see from my formula in cells L and M that for each event (football match), the home team will contribute 7% of their total match rating (the number from column I) and 5% of the away rating (the number in column J). Depending on the result, those points go to either the home for a home win, away team for an away win, or split for a draw and the rateforms will be adjusted accordingly for each team.

    Hope this explains the problem better. As you can see, I thought sumproduct was the way to go with this but if anyone recommends other methods like match/index/row functions I am all ears.

    As for your suggestion, i tried it but it doesn't give me the result i'm looking for as it is not referencing the rateform numbers - those are the numbers i want to appear in the columns I and J.

    If you or anyone else has ideas for other approaches to this problem please enlighten me.

    Thanks for your response nonetheless!
    Last edited by Spankyf; 05-14-2012 at 11:58 PM.

  4. #4
    Registered User
    Join Date
    05-14-2012
    Location
    Somewhere, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Sumproduct and summing last occurrences of criteria

    Bumping this in the hope someone has an idea!

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sumproduct and summing last occurrences of criteria

    Try this in I3, dragged right to J3 and down

    =IF(SUMPRODUCT(--($B$2:$C2=B3))<1,1000,IF(IFERROR(MATCH(B3,$B$2:$B2,0),0)>IFERROR(MATCH(B3,$C$2:$C2,0),0),INDEX($L$2:$L2,MATCH($B3,$B$2:$B2,0)),INDEX($M$2:$M2,MATCH($B3,$C$2:$C2,0))))

    Does that work for you?

    EDIT: Nope, that compares first match in B to first match in C. We need last match. Back to drawing board.

    EDIT: Updated with this formula in I3
    =IF(SUMPRODUCT(--($B$2:$C2=B3))<1,1000,IF(IFERROR(LOOKUP(2,1/($B$2:$B2=B3),ROW($A$1:$A1)),0)>IFERROR(LOOKUP(2,1/($C$2:$C2=B3),ROW($A$1:$A1)),0),INDEX($L$2:$L2,LOOKUP(2,1/($B$2:$B2=B3),ROW($A$1:$A1))),INDEX($M$2:$M2,LOOKUP(2,1/($C$2:$C2=B3),ROW($A$1:$A1)))))

    It seems to work.
    Last edited by ChemistB; 05-15-2012 at 12:59 PM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    05-14-2012
    Location
    Somewhere, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Sumproduct and summing last occurrences of criteria

    I'll try that now. Thanks for the reply...

    Edit: this works perfectly. Thank you so much ChemistB.
    Last edited by Spankyf; 05-15-2012 at 01:33 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1