+ Reply to Thread
Results 1 to 71 of 71

Updated count when new row added

  1. #1
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Updated count when new row added

    Hi. So you have a column of football results. WIN, DRAW or LOSE. Win is worth 3points, Draw 1.

    Two criteria. ALL games or LEAGUE games only.

    You want to add up the points gained from the most recent 5 matches in both categories.

    I've attached an example workbook.

    Questions.

    1) Do I need Columns D and F or can I add up the W and D. Maybe COUNTIF W*3 and D?
    2) Can I use A24 to dictate how many results are looked at?
    3) This is the real sticky one. As results are added, is it possible for the formula to move to include the new result so the figure put in A24 works from the bottom up? I was thinking maybe with the DATEDIF and NOW option but there may be a simpler way?

    I've included a small example but the actual workbook has over 5000 rows and I maybe want to look at a lot more rows than 5 from time to time.

    Many thanks.
    Attached Files Attached Files
    Last edited by Marvo; 12-22-2020 at 08:04 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    I am a little confused. It's always the last 5 rows... not the last 5 rows of league fixtures?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Its current form Glenn, so last 5,10 matches, whatever you're looking for. If its not a league game, it still needs working out as you might want to work out the best run of results and the only way to do that is give them a value. For example, my team is going through a particularly rough patch at the moment so I may want to look for the last time this was worse. We've lost the last 4 but thanks to an earlier formula you gave me which I've adapted and added to I can easily see the last time we lost 4 on the bounce.

    I've got a formula on something else that adds up so many games, whatever I put in the search cell, however it has a stable starting point. This requires movement to the latest row entered.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    That doesn't actually answer my Q. So I have assumed that Part 2 (league games) is still last 5 rows, not the last 5 league games (from 5, 10, or 15 rows).

    Maybe this:
    =SUMPRODUCT((INDEX($C:$C,COUNTIF($C:$C,"?*")-$J$15):INDEX($C:$C,COUNTIF($C:$C,"?*"))={"W","D"})*{3,1})

    and

    =SUMPRODUCT((INDEX($C:$C,COUNTIF($C:$C,"?*")-$J$15):INDEX($C:$C,COUNTIF($C:$C,"?*"))={"W","D"})*(INDEX($E:$E,COUNTIF($C:$C,"?*")-$J$15):INDEX($E:$E,COUNTIF($C:$C,"?*"))="League")*{3,1})
    Attached Files Attached Files

  5. #5
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    You nearly threw me there, couldn't work out where you got J15 from but realised that must be my A24?

    First formula works fine total wise with 5 in the search cell (A24). However if I change that to 6 it goes up by 3 to 14 when it should actually remain unchanged as the match 6 games ago was a defeat. If I add a row, say a draw with 5 in the search cell it should now total 9 as you gain 1pt but lose 3 but it shows 12 so maybe J15 in your formula doesn't refer to A25 in the workbook?

    I think I understand your question now. Yes, the second formula should be looking to go back (in this case) 5 LEAGUE games. The total I had down was wrong, should have been 8. Understandably in that case the second formula doesn't work.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    OK. the first bit is easy. Its like how many fence posts do you need to put up 5 fence panels... 6.

    =SUMPRODUCT((INDEX($C:$C,COUNTIF($C:$C,"?*")-$J$15+1):INDEX($C:$C,COUNTIF($C:$C,"?*"))={"W","D"})*{3,1}) Off to think about part B
    Attached Files Attached Files

  7. #7
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    First part ticks all the boxes Glenn. Cheers. Good luck with part 2.

  8. #8
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Transferred the first formula to the master workbook. The figure was correct for 5 matches. Changed it to 10 games and again it was correct with 11 points. I then added a result (a draw) which replaced a loss so the number should have gone up 1 but sadly stayed on 11.

    This is the formula transferred to my workbook

    =SUMPRODUCT((INDEX(ALL!$J:$J,COUNTIF(ALL!$J:$J,"?*")-$V$3+1):INDEX(ALL!$J:$J,COUNTIF(ALL!$J:$J,"?*"))={"W","D"})*{3,1})

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    Scrap the formula.

    All fixtures:
    =SUM(MOD(AGGREGATE(14,6,(ROW($D$2:$D$1000)*10^7+$D$2:$D$1000)/($C$2:$C$1000<>""),ROW(INDIRECT("1:"&J15))),10^7))

    League fixtures:
    =SUM(MOD(AGGREGATE(14,6,(ROW($D$2:$D$1000)*10^7+$D$2:$D$1000)/($E$2:$E$1000="League"),ROW(INDIRECT("1:"&J15))),10^7))
    Attached Files Attached Files

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Updated count when new row added

    Please try at D24

    =SUM(INDEX(D2:D21,MATCH(9,D2:D21)):INDEX(D2:D21,MAX(1,MATCH(9,D2:D21)-$A24+1)))
    Attached Files Attached Files

  11. #11
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    So guys, are we back to looking at the points figure rather than giving W & D a value?

    I've got to be honest, in the Master book there is no such column but I guess it would be easy to add it.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    Why did you put it in your sample if it isn't there in reality???

  13. #13
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Tried to make it simpler, I'm always getting in trouble on here for not explaining myself clearly and like I said, its not a biggie, easy to add.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    Just tell us as it is from the outset - constantly shifting goalposts are incredibly frustrating for those trying to help you!!!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  15. #15
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Okay. It's very difficult, the workbook is vast, far too big to put on here, though that would save me a heap of trouble. Have to keep changing references over. I did say in the very first question I would like the letters to reference a value if possible.

    Anyway both guys, your formulas work in the first instance but I cant get Glenn's second to work.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    Leave stuff where it is in the master workbook, but just minimise the amount of data. That way you'll cause yourself (and us) far less confusion. It's a small subset of your data that is needed.

  17. #17
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I've put Bo-RY and Glenn's formula in. I'm getting the wrong result in both latter formula now.
    Attached Files Attached Files

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    What results are you expecting? Values?

  19. #19
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    The figure in J4 and O4 should be 7. Last four matches Wx2= 6, Dx1=1.
    The figure in O6 should be 6. Last four LEAGUE matches, Wx2 =6 Lx2 = 0.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    Glenn's formulae will work if you remove that @ symbol. Change this:

    =SUM(MOD(AGGREGATE(14,6,(ROW($D$2:$D$1000)*10^7+$D$2:$D$1000)/($C$2:$C$1000<>""),@ROW(INDIRECT("1:"&G4))),10^7))

    to this:

    =SUM(MOD(AGGREGATE(14,6,(ROW($D$2:$D$1000)*10^7+$D$2:$D$1000)/($C$2:$C$1000<>""),ROW(INDIRECT("1:"&G4))),10^7))

    and this:

    =SUM(MOD(AGGREGATE(14,6,(ROW($D$2:$D$1000)*10^7+$D$2:$D$1000)/($E$2:$E$1000="League"),@ROW(INDIRECT("1:"&G4))),10^7))

    to this:

    =SUM(MOD(AGGREGATE(14,6,(ROW($D$2:$D$1000)*10^7+$D$2:$D$1000)/($E$2:$E$1000="League"),ROW(INDIRECT("1:"&G4))),10^7))

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    I have not looked at the recent posts here. I have created 5 named ranges to make the formulae less cumbersome. Forumae USING column D are in orange. Formulae using WDL are in green. test them on this sheet first.
    Attached Files Attached Files

  22. #22
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Ali, they are array formulas then? They work with control shift enter.

  23. #23
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    They work fine Glenn, up to 7. If you put 8 in it returns #NUM. This is probably because there are only 7 league matches however there are 10 matches altogether so I don't know why that result shows #NUM as well.

    Edit: Your formula that Ali edited work as well, that also shows #NUM when you exceed the amount of league matches but the first formula continues to show a result.
    Last edited by Marvo; 12-20-2020 at 12:20 PM.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    Ali, they are array formulas then? They work with control shift enter.
    Yes, but it's unlikely that you need to hit CSE in Excel 2019. But you do need to remove the rogue @ signs!

    Both work for me up to 8, and with 9 the league one fails because there are only 8 league matches, but the other works.

    Excel 2016 (Windows) 32 bit
    G
    H
    I
    J
    3
    ROWS
    4
    9
    Glenn
    13
    ALL
    5
    6
    Glenn
    #NUM!
    LEAGUE
    Sheet: Sheet1
    Last edited by AliGW; 12-20-2020 at 12:23 PM.

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    Some time back in this thread you said "...the second formula should be looking to go back (in this case) 5 LEAGUE games". That is why it shows an error in column O, if 8 is entered in J15. There are only 7 league games.

    The formulae in column M continue to work perfectly if 8 is entered in J15.

    What/where is the problem???
    Attached Images Attached Images

  26. #26
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Yes, did that. I use Excel2019 now since my hard drive broke and Microsoft wouldn't let me reinstall my 2016 edition, wanted me to rent 365 or buy 2019. I did have to use CSE but maybe that's because the book was constructed using 2016???

    I don't suppose this will help anybody else but Microsoft wanted $250 for excel 2019. I bought it off eBay, downloaded it off the Microsoft site for £1.99.

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    You probably bought a pirated copy, then!

    For me, the subscription model for 365 at £79 per year (which can be installed on multiple devices) is great value for money, and it's always up-to-date. I'd never buy a standalone version again.

  28. #28
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Updated count when new row added

    For non-duplicate date

    J4
    =SUMIFS(D:D,A:A,">="&LARGE(A:A,G4))

    L4
    =SUMIFS(D:D,A:A,">="&LARGE(A:A,G4),E:E,K4)
    Attached Files Attached Files

  29. #29
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Thanks Glenn, I got confused about the two rows, only just realised the second is counting WDL, brilliant, just what I wanted. I'll transfer it over to my main workbook, I've used NAME manger before but will rad up on it to make sure I get it right. Many thanks everybody (We got there in the end).

  30. #30
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Thanks Bo-Ry, yours worked with column D but I'm going to stick with Glenn on this one because his gives value to W & D which saves me adding yet another column to my workbook.

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    I'm sure there'll be another twist!!!

  32. #32
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I have to admit I'm very scared.

  33. #33
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    I'd like to bet there's a much more efficient way of doing everything you are doing in that massive workbook, but as long as it works ...

  34. #34
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    Quote Originally Posted by AliGW View Post
    You probably bought a pirated copy, then!

    For me, the subscription model for 365 at £79 per year (which can be installed on multiple devices) is great value for money, and it's always up-to-date. I'd never buy a standalone version again.
    Off topic, Ali. is there any way of selecting an option on 365 to show you that array entry is required on previous versions? It's only an issue here. In the real word, it doesn't matter.

  35. #35
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    Thanks for rep, Marvo. It must be nearly beer o'clock in Belfast...

  36. #36
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    Not that I know of, Glenn - it's all trial and error. I have seen that spurious @ sign before - I think it's 365 trying to be helpful, but not helping in the slightest.

  37. #37
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    You're right Ali but I'm getting there. My golf workbook that had over 80 pages now has just 8 and what I'm doing to day is reducing a page of 141 rows down to 34. If only my diet worked that well.

    As for my Excel 2019, I've no shame. I explained to Microsoft how my hard drive had died with MY Excel 2016 on it and they basically said "TOUGH" so to hell with them. This new one works a treat.

    Oh and Glenn, I mentioned on Twitter how I'd lost all my work and particularly my photo's. A guy privately messaged me from a town 12 miles away, said he could help. Got everything back, took about a week. Gave him £30, he didn't ask for anything. See ya Tomorrow.

  38. #38
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Updated count when new row added

    Well, an unexpected Xmas pressie. take care!!

  39. #39
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Glenn, just a quick one, do I need both of those formulas in M & O or just one? They don't appear to refer to each other.

    Also, I've copied everything over, in the name manager formula you've got a reference to E2:E13. Do I extend that to my last current row? I've made it E2:E6000 but I'm getting a return of 0.

  40. #40
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I've got the League one working looking back but doesn't change when you add a result. The all one doesn't work YET.

  41. #41
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982
    Now got the League one working perfectly by extending the column range.

  42. #42
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I've reopened this thread as I'm struggling, now unable to get any result from either formula other than 0. I suspect it might be down to the length of column in E in the example (Column I in my book) or whether I need to use all four formula but they don't appear to be reliant upon each other.

    This is what I have now in Name Manager

    Attachment 709758

    These are the two formula I'm using

    =SUMPRODUCT((INDEX(CFORM3,):INDEX(CFORM1,)="League")*(INDEX(CFORM4,):INDEX(CFORM2,)={"W","D"})*{3,1})

    =SUMPRODUCT((INDEX(CFORM5,):INDEX(CFORM2,)={"W","D"})*{3,1})
    Last edited by Marvo; 12-21-2020 at 05:47 AM.

  43. #43
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    LOL - what did I say???

    Attach the workbook.

  44. #44
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I tried Ali, I can't make it small enough. The picture of the name manager shows Glenn's workings substituting the actual workbook and the two formulas are also Glenn's.

  45. #45
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    There you go, try that.
    Attached Files Attached Files

  46. #46
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    Unfortunately it's throwing a #REF! error in the Name Manager:

    CFORM5: =INDEX(ALL!$B:$B,COUNTIF(ALL!$B:$B,"?*")-ALL!#REF!+1)

  47. #47
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Whoops! Hang on.

  48. #48
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Updated count when new row added

    Try
    League
    =SUMPRODUCT(COUNTIFS(INDEX(ALL!B:B,MATCH(9,ALL!C:C)):INDEX(ALL!B:B,MATCH(99,ALL!C:C)-B3+1),{"W","D"},INDEX(ALL!A:A,MATCH(9,ALL!C:C)):INDEX(ALL!A:A,MATCH(99,ALL!C:C)-B3+1),C3),{3,1})

    and All
    =SUMPRODUCT(COUNTIFS(INDEX(ALL!B:B,MATCH(99,ALL!C:C)):INDEX(ALL!B:B,MATCH(99,ALL!C:C)-B3+1),{"W","D"}),{3,1})

    OR use range name
    Competition =INDEX(ALL!$A:$A,MATCH(9,ALL!$C:$C)):INDEX(ALL!$A:$A,MATCH(99,ALL!$C:$C)-'Consecutive & Runs'!$B$3+1)
    Result =INDEX(ALL!$B:$B,MATCH(9,ALL!$C:$C)):INDEX(ALL!$B:$B,MATCH(99,ALL!$C:$C)-'Consecutive & Runs'!$B$3+1)

    League
    =SUMPRODUCT(COUNTIFS(Result,{"W","D"},Competition,C3),{3,1})
    All
    =SUMPRODUCT(COUNTIFS(Result,{"W","D"}),{3,1})
    Attached Files Attached Files

  49. #49
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Why are we referencing Column C in the range name please Bo-Ry? It's goals scored, I don't see the relevance????

  50. #50
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Quote Originally Posted by AliGW View Post
    Unfortunately it's throwing a #REF! error in the Name Manager:

    CFORM5: =INDEX(ALL!$B:$B,COUNTIF(ALL!$B:$B,"?*")-ALL!#REF!+1)
    Its

    CFORM5: =INDEX(ALL!$B:$B,COUNTIF(ALL!$B:$B,"?*")-Consecutives & Runs!$B$3+1)

  51. #51
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    That's why - that worksheet doesn't exist in the sample workbook. You've changed the spelling somewhere along the way.

  52. #52
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    yes I've added an s.

  53. #53
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I'm getting a result in my main workbook now but it isn't an expected result. Trying to work out what's wrong.

  54. #54
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Okay bear with me, I'm now getting the hang of these attachments. This one is updated and each item is in the same row and cell as it is in my master workbook so if this can be solved the formula will be easy to transfer..

    The results on the Records page are incorrect.
    Attached Files Attached Files

  55. #55
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    What are the values that you are expecting for this dataset?

  56. #56
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    You have 6 in V3 so you are looking for the last 6 results in column J for all and League only

    The result in X3=4
    The result in x5=6

    Now change V3 to 7
    X3=7
    X5=7

    Now place 1 in cells K5375 & L5375
    X3=5
    X5=7

    Remember D=1 and W=3.

  57. #57
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Easier way o look at it. If V3=1 both results should return 0 as the game was lost. Same if you put V3=2 and it should be the same if V3=3 as that game was lost as well but as you can see it isn't correct.

  58. #58
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Bo-Ry, You're formula for All Competitions works beautifully, however your formula for League games only doesn't work at all. Any idea why?
    Attached Files Attached Files

  59. #59
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Updated count when new row added

    Key League in C3 or
    change C3 to W3 or "League"

    =SUMPRODUCT(COUNTIFS(INDEX(ALL!J:J,MATCH(9,ALL!K:K)):INDEX(ALL!J:J,MATCH(99,ALL!K:K)-V3+1),{"W","D"},INDEX(ALL!I:I,MATCH(9,ALL!K:K)):INDEX(ALL!I:I,MATCH(99,ALL!K:K)-V3+1),W3),{3,1})
    Attached Files Attached Files

  60. #60
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    That gives me a result now but if you add a further result it goes wrong????

  61. #61
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I know where it's going wrong now but don't know how to put it right.

    When its a League game the formula is ignoring the result for points but it's still counting it as a game played.
    So as an example if you imagine you've got 10 matches in total, 3 are cup games and they come every 3 games.
    So lets say you win every match. So counting latest result first, 3, 3 n/a, 3, 3, n/a 3, 3, n/a, 3. So if you ask how many points from the last 5 League games, the answer should be 15 from games 1,2 4,5 & 7. The formula is giving an answer of 12 from games 1 to 5. So its ignoring the points from game 3 as it should as a cup game but is still counting it as a league match played. Its ignoring the result but not the game.

  62. #62
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I thought I'd give this one more try.

    The incorrect result I'm getting is in X3. The expected result would be 11.

    The formula is adding up the points from Column J, W=3 and D=1 from the latest specified amount of games (in V3) with the criteria they must be League games (Column I) and allow for further additions to the data.

    The piece of the formula not working is the counting of league games.

    Many thanks.
    Attached Files Attached Files

  63. #63
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Updated count when new row added

    Try

    =SUMPRODUCT(COUNTIFS(INDEX(ALL!J:J,LARGE(INDEX(ROW(ALL!J2:J5404)*(ALL!J2:J5404<>"")*(ALL!I2:I5404=W3),),V3)):ALL!J5404,{"W","D"},INDEX(ALL!I:I,LARGE(INDEX(ROW(ALL!J2:J5404)*(ALL!J2:J5404<>"")*(ALL!I2:I5404=W3),),V3)):ALL!I5404,W3),{3,1})

    Change 12 to V3
    Attached Files Attached Files
    Last edited by Bo_Ry; 12-22-2020 at 07:43 AM. Reason: Change 12 to V3

  64. #64
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Whilst that returns the correct result 11 in X3, it also returns 11 irrespective of how many games you put in V3.

    I've just took a further look, the formula doesn't appear to reference V3 at all????
    Last edited by Marvo; 12-22-2020 at 07:46 AM.

  65. #65
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    Marvo - did you read the last line of post #63???

    =SUMPRODUCT(COUNTIFS(INDEX(ALL!J:J,LARGE(INDEX(ROW(ALL!J2:J5404)*(ALL!J2:J5404<>"")*(ALL!I2:I5404=W3),),V3)):ALL!J5404,{"W","D"},INDEX(ALL!I:I,LARGE(INDEX(ROW(ALL!J2:J5404)*(ALL!J2:J5404<>"")*(ALL!I2:I5404=W3),),V3)):ALL!I5404,W3),{3,1})

    Bo_Ry just added this.

  66. #66
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    I didn't, but then I did, and then you posted. I'll give it a go.

  67. #67
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    Changing 12 to V3 brings up the error

    A value used in the formula is of the wrong data type

  68. #68
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    No, it doesn't - have you somehow deleted a comma or closing bracket? Or is V3 derived from a formula? If so, what is the formula?

    Excel 2016 (Windows) 32 bit
    V
    W
    X
    1
    CURRENT FORM
    2
    GAMES
    POINTS
    3
    12
    LEAGUE
    11
    4
    5
    ALL COMPETITIONS
    12
    Sheet: Records


    Excel 2016 (Windows) 32 bit
    X
    3
    =SUMPRODUCT(COUNTIFS(INDEX(ALL!J:J,LARGE(INDEX(ROW(ALL!J2:J5404)*(ALL!J2:J5404<>"")*(ALL!I2:I5404=W3),),V3)):ALL!J5404,{"W","D"},INDEX(ALL!I:I,LARGE(INDEX(ROW(ALL!J2:J5404)*(ALL!J2:J5404<>"")*(ALL!I2:I5404=W3),),V3)):ALL!I5404,W3),{3,1})
    Sheet: Records
    Last edited by AliGW; 12-22-2020 at 07:57 AM.

  69. #69
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    There were TWO 12. I had only changed the one highlighted in red. Going to test it out now.

  70. #70
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    982

    Re: Updated count when new row added

    It works perfectly. Thank you so, so much. I'm nearly finished now, might need VBA to add the final finishing touch. Many thanks again guys and have a very happy Christmas. Stay safe (wherever you reside).

  71. #71
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Updated count when new row added

    The finishing touch will need a new thread, so see you after Christmas - have a good one, if you can!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find changes in table - deleted , updated and added columns and variables
    By Paulant in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2019, 04:10 AM
  2. [SOLVED] UPDATED: Added File New Row Macro Autoupdate Line...
    By Pgm_Mgr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2019, 10:23 AM
  3. [SOLVED] Outlook email notification when file is updated/content is added
    By rayted in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2018, 12:26 PM
  4. Replies: 3
    Last Post: 02-17-2014, 09:20 PM
  5. [SOLVED] master sheet updated itself with new added sheets
    By greatairi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-22-2013, 02:50 AM
  6. master sheet updated itself with new added sheets
    By greatairi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2013, 03:04 AM

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