+ Reply to Thread
Results 1 to 7 of 7

Count number of occurrences but if there are consecutive matches count them as one

  1. #1
    Registered User
    Join Date
    11-26-2013
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2013
    Posts
    21

    Question Count number of occurrences but if there are consecutive matches count them as one

    I'm a teacher and I have a record of student absences like this:

    Name Nov. 4 Nov. 5 Nov. 6 Nov. 7
    Divine absent present absent late
    Riza absent present absent absent
    Aira absent absent absent absent

    In their summary records, I want two different pieces of data: number of absences (simple countif) and number of occurrences, wherein if it's consecutive, it's counted as one.

    For example, Divine would have an A of 2 and an O of 2. Riza would have an A of 3 and an O of 2. Aira would have an A or 4 and an O of 1.

    Any macro or formula is much appreciated.
    Thanks!

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Count number of occurrences but if there are consecutive matches count them as one

    Can you please explain what is O?

    And how you are getting the counts for it?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    11-26-2013
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Count number of occurrences but if there are consecutive matches count them as one

    A = absences (number of times the word "absent" came up from the table)
    O = occurrences (number of times the word "absent" came up, counting consecutives as ONE)

    I got the figures by counting from the table.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Count number of occurrences but if there are consecutive matches count them as one

    Ok.. everything is clear just attach a sample workbook so that you will get the exact solution in seconds.

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Count number of occurrences but if there are consecutive matches count them as one

    welcome to the forum. say your data is in A1:E4 with the headers, and the summary is in row 8. then B8 would be:
    =COUNTIF(B2:E2,B$7)

    C8 is an array formula (use the range sparingly since this slows down the workbook):
    =SUM(IF(FREQUENCY(IF(B2:E2="absent",COLUMN(B2:E2)),IF(B2:E2<>"absent",COLUMN(B2:E2)))>0,1))

    i am assuming the sequence of the data & the summary names are the same (Divine, Riza, Aira). if they are not, replace all my B2:E2 with:
    =INDEX($B$2:$E$4,MATCH($A8,$A$2:$A$4,0),)
    that means:
    =COUNTIF(INDEX($B$2:$E$4,MATCH($A8,$A$2:$A$4,0),),B$7)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    array formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    if you want the summary in another worksheet, simply cut & paste over to another worksheet.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Count number of occurrences but if there are consecutive matches count them as one

    Try this code.....
    Please Login or Register  to view this content.
    Please see the attached sheet.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Registered User
    Join Date
    11-26-2013
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Count number of occurrences but if there are consecutive matches count them as one

    Thanks for the replies! I used benishiryo's solution.

+ 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. Count the number of occurrences between In and Out times.
    By allwrighty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2013, 05:18 PM
  2. Count number of occurrences in a day
    By quinnbri in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2013, 05:49 PM
  3. Count consecutive number of negative occurrences >=3
    By nbiggss in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-02-2012, 02:14 PM
  4. Count the number of consecutive occurrences
    By timmycl_7 in forum Excel General
    Replies: 12
    Last Post: 05-03-2012, 02:04 PM
  5. [SOLVED] Count number of occurrences
    By MarkM in forum Excel General
    Replies: 1
    Last Post: 07-27-2006, 05:15 PM

Tags for this Thread

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