+ Reply to Thread
Results 1 to 8 of 8

counting occurrences in months

  1. #1
    Registered User
    Join Date
    06-05-2023
    Location
    England
    MS-Off Ver
    365
    Posts
    28

    counting occurrences in months

    Hi Guys,

    Hoping for your help again.

    I am trying to count how often a data set appears in each month.

    I am trying to do it twice once with one argument and then again with two arguments ( i think)

    I will try and explain against the sheet i have attached.

    So...

    first of all in cells E5 - P5 i would like to count how many times "Area one" appears in the list in the blue table in column N in each month in column H. So for example i can see "Area one" appears 3 times in the table in May 24 in column H, so in Cell F5 i would like it count 3.

    Second off, I kind of what the same thing, so in cells E10 to P10 i want to do the same count, but this time only include ones that have "stage 3" in column L, so again if we take Area one as an example and the month of May, I can see Area one appears 3 times in may, but only twice with "Stage 3" in column L, So in cell F10 i would want it to count only 2

    I hope that makes sense, sometimes i find it hard to articulate what im after :|

    Thanks in advance

    Luke
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,928

    Re: counting occurrences in months

    at the first look I think you need a Pivot Table.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-05-2023
    Location
    England
    MS-Off Ver
    365
    Posts
    28

    Re: counting occurrences in months

    Thanks for the response, that is really helpful, however, i uploaded a dummy sheet, the real sheet layout is really complicated and i struggle to get the pivot tables to work in it, i wondered if there were any formulas that could do the counting for me?

  4. #4
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: counting occurrences in months

    If in your Excel function PIVOYBY already available (O365 V2401 or newer)

    Please try

    All stages:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Only Stage 3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-05-2023
    Location
    England
    MS-Off Ver
    365
    Posts
    28

    Re: counting occurrences in months

    Unfortunately i dont have that functionality available, i thought i might be able to get away with some kind of COUNTIFS or SUMPRODUCT

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: counting occurrences in months

    Try, =COUNTIFS($N$16:$N$28,$D5,$H$16:$H$28,">="&E$4,$H$16:$H$28,"<="&EOMONTH(E$4,0)) in E5, copy across and down.

    With Stage 3 in D9, =COUNTIFS($N$16:$N$28,$D10,$H$16:$H$28,">="&E$9,$H$16:$H$28,"<="&EOMONTH(E$9,0),$L$16:$L$28,$D$9) in E10, copy across and down.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-05-2023
    Location
    England
    MS-Off Ver
    365
    Posts
    28

    Re: counting occurrences in months

    This was exactly what i needed! works perfectly thank you :D
    Last edited by AliGW; 02-12-2024 at 10:43 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.

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

    Re: counting occurrences in months

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

+ 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 total number of months, not counting overlapping months
    By MacroIdiot22 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-19-2024, 06:52 PM
  2. Counting the number of months before a certain months in a cell
    By Ikie86 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-19-2023, 03:47 AM
  3. [SOLVED] Calculating number of occurrences in the last 12 months (weekdays only)
    By VB17 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-19-2021, 06:58 AM
  4. Counting occurrences not values.. Counting blocks of words in columns
    By Flydd in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-01-2016, 06:42 PM
  5. Calculate number of occurrences of text in the past 12 months
    By wildbilll in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2015, 03:47 PM
  6. Replies: 6
    Last Post: 03-11-2014, 07:31 AM
  7. Counting occurrences
    By HaydenB in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-30-2010, 04:26 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