+ Reply to Thread
Results 1 to 1 of 1

Formula to group events in a running total that has several "ifs"

  1. #1
    Registered User
    Join Date
    10-30-2017
    Location
    Loughborough
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    10

    Formula to group events in a running total that has several "ifs"

    Hello again excellents!

    I am working on a spreadsheet that is a collection of events in a game of cricket. I am aware that lots of people do not know cricket, so I will try and explain (I have attached a dummy spreadsheet) ... but the headings are as follows:
    Col A: MatchInns
    Col B: Bowler
    Col C: Over
    Col D: Day
    Col E: Session
    Col F: Spell
    Col G: Spell2 (=COUNTIFS($B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2,$E$2:E2,E2,$A$2:A2,A2))

    Basically, a Bowler A will have a series of overs that run alternatively with another Bowler B, when Bowler A is replaced by Bowler C, he is said to have bowled a Spell. So in a pattern of A-B-A-B-C-D: Bowler A will have 1 Spell and Bowler B will have 1 Spell ...if Bowler A return (A-B-A-B-C-D-A), then he starts a 2nd spell and this continues 1,2,3 .... n. A spell can be broken by either a new bowler, change in MatchInns (ColA), Change in Day (ColD) or a change in Session (ColE), I have manually completed Column F and tried to use the Countifs formula in Col G but, I am not getting the results I need.

    I realise I am struggling to explain this very well but, basically a series is dependent on several factors that stop it but allow it proceed to the next number but, only col A stops the series completely and restarts it at 1 again.

    I am very sorry that I am not able to explain this better, I appreciate that it might be difficult to get your heads around my drivel but if anyone can it is this forum!

    Thanks in advance.
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 11-19-2020, 08:30 PM
  2. Replies: 1
    Last Post: 09-13-2013, 12:23 PM
  3. [SOLVED] Macro to delete entire row if cell contains the word "total" or "Total"
    By theatricalveggie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2011, 12:38 PM
  4. Replies: 1
    Last Post: 02-07-2010, 12:44 AM
  5. Floating cell containing a "Running Total".
    By Dantheman in forum Excel General
    Replies: 2
    Last Post: 06-26-2006, 10:25 PM
  6. [SOLVED] Pivot table "Group and Show Details" vs. "SubTotals"
    By pgchop in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2006, 03:35 AM
  7. [SOLVED] Linking two "total" pages to create a "Complete Total" page
    By Jordon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-10-2006, 07:20 PM

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