+ Reply to Thread
Results 1 to 20 of 20

Sum of Count On Monthly Interval

Hybrid View

  1. 06-19-2018, 02:22 PM #1
    Jnehra
    Jnehra is offline
    Registered User
    Join Date
    09-10-2017
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    63

    Sum of Count On Monthly Interval

    Hi All,

    I am trying to use a formula to help get a count of the number of total incidents on a monthly basis. In the attachment below, please reference the All Regions Incident Type sheet.

    An example of what I'd like to see is the sum of column D for each month (using either the actual month name in column C or the month number in column E). Then I want to take the average of that number to get a daily average of incidents for the month.

    Example of Formula 1 = SUM of ALL JANUARY incidents using COUNT from column D.
    Example of Formula 2 = AVERAGE OF ALL JANUARY incidents using SUM from Formula 1 and number of days in the month of JANUARY.

    Hope the request make sense. I have been playing around with a couple of different formulas I found online to no avail and this forum has always come through in the past.

    Thanks in advance for any response with help! Much Appreciated
    -John
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-19-2018, 02:26 PM #2
    AliGW
    AliGW is online now
    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. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,754

    Re: Sum of Count On Monthly Interval

    In D290:

    =AVERAGEIF($E$2:$E$289,E290,$D$2:$D$289)

    Is this what you want?
    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 With QuoteRegister To Reply
  3. 06-19-2018, 02:35 PM #3
    Jnehra
    Jnehra is offline
    Registered User
    Join Date
    09-10-2017
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    63

    Re: Sum of Count On Monthly Interval

    Not exactly Ali. It looks like you are providing an average daily count for all incidents over all months. I am looking for that same information but on a month by month basis.
    Reply With QuoteRegister To Reply
  4. 06-19-2018, 02:31 PM #4
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sum of Count On Monthly Interval

    is that what you want to achieve?


    I
    J
    K
    1
    		 Month Sum Avg
    2
    		 January
    240
    6.67


    or


    I
    J
    K
    1
    		 Month Sum Avg
    2
    		 January
    240
    6.67
    3
    		 February
    106
    2.94
    4
    		 March
    152
    4.22
    5
    		 April
    164
    4.56
    6
    		 May
    158
    4.39
    7
    		 June
    0
    0
    8
    		 July
    9
    		 August
    Last edited by sandy666; 06-19-2018 at 02:33 PM.
    Reply With QuoteRegister To Reply
  5. 06-19-2018, 02:35 PM #5
    Jnehra
    Jnehra is offline
    Registered User
    Join Date
    09-10-2017
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    63

    Re: Sum of Count On Monthly Interval

    That is EXACTLY what I am looking for Sandy!

    Can you please share your formulas?
    Reply With QuoteRegister To Reply
  6. 06-19-2018, 02:39 PM #6
    modytrane
    modytrane is offline
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Sum of Count On Monthly Interval

    Drag the formulas down to D294:E294
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane
    Reply With QuoteRegister To Reply
  7. 06-19-2018, 02:43 PM #7
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Sum of Count On Monthly Interval

    Quote Originally Posted by Jnehra View Post
    That is EXACTLY what I am looking for Sandy!

    Can you please share your formulas?
    sorry mate but this is a PowerQuery not a formula

    Power Query for
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 06-19-2018, 02:37 PM #8
    AliGW
    AliGW is online now
    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. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,754

    Re: Sum of Count On Monthly Interval

    My formula is for January - did you not try it?
    Reply With QuoteRegister To Reply
  9. 06-19-2018, 02:44 PM #9
    Jnehra
    Jnehra is offline
    Registered User
    Join Date
    09-10-2017
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    63

    Re: Sum of Count On Monthly Interval

    Yeah Ali. That gave me the results for January. Would I just need to change the E290 in the middle of the formula for subsequent months?
    Also is there a way to show the SUM for each month along with the average?

    Thanks...
    Reply With QuoteRegister To Reply
  10. 06-19-2018, 02:38 PM #10
    modytrane
    modytrane is offline
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Sum of Count On Monthly Interval

    Formulas:

    D290:
    HTML Code: 
    E290:
    HTML Code: 
    Reply With QuoteRegister To Reply
  11. 06-19-2018, 02:45 PM #11
    AliGW
    AliGW is online now
    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. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,754

    Re: Sum of Count On Monthly Interval

    Yes:

    =SUMIF($E$2:$E$289,E290,$D$2:$D$289)
    Reply With QuoteRegister To Reply
  12. 06-19-2018, 02:50 PM #12
    modytrane
    modytrane is offline
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Sum of Count On Monthly Interval

    My formulas will give you totals in column D and average in column E.
    Reply With QuoteRegister To Reply
  13. 06-19-2018, 02:52 PM #13
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sum of Count On Monthly Interval

    Promotion?

    My PowerQuery will give you all-in-one without any formula as you can see above
    Reply With QuoteRegister To Reply
  14. 06-19-2018, 02:58 PM #14
    modytrane
    modytrane is offline
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Sum of Count On Monthly Interval

    Sandy:
    Your solution looks interesting and seems to e efficient but the average result for January is incorrect.

    240/31 should be 7.74 not 6.67.

    Am I missing something?
    Reply With QuoteRegister To Reply
  15. 06-19-2018, 02:59 PM #15
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sum of Count On Monthly Interval

    yes you are, this is average of group

    and why 31?


    I
    J
    K
    L
    1
    		 Month Sum Avg Count
    2
    		 January
    240
    6.67
    37
    3
    		 February
    106
    2.94
    37
    4
    		 March
    152
    4.22
    37
    5
    		 April
    164
    4.56
    37
    6
    		 May
    158
    4.39
    37
    7
    		 June
    0
    0
    36
    8
    		 July
    36
    9
    		 August
    36
    Last edited by sandy666; 06-19-2018 at 03:03 PM.
    Reply With QuoteRegister To Reply
  16. 06-19-2018, 03:03 PM #16
    modytrane
    modytrane is offline
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Sum of Count On Monthly Interval

    The original request OP asked for total incidents in the month divided by number of days in January.

    Quote from post #1:
    Example of Formula 2 = AVERAGE OF ALL JANUARY incidents using SUM from Formula 1 and number of days in the month of JANUARY.
    Reply With QuoteRegister To Reply
  17. 06-19-2018, 03:11 PM #17
    modytrane
    modytrane is offline
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Sum of Count On Monthly Interval

    And the count for January in C2:C289 is 36.
    Reply With QuoteRegister To Reply
  18. 06-19-2018, 03:14 PM #18
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sum of Count On Monthly Interval

    really ?
    Reply With QuoteRegister To Reply
  19. 06-20-2018, 03:16 PM #19
    Jnehra
    Jnehra is offline
    Registered User
    Join Date
    09-10-2017
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    63

    Re: Sum of Count On Monthly Interval

    SOLVED -- Thanks to all for your help!
    Reply With QuoteRegister To Reply
  20. 06-20-2018, 03:17 PM #20
    AliGW
    AliGW is online now
    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. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,754

    Re: Sum of Count On Monthly Interval

    Thanks for the rep - glad to have helped!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count until certain interval reached
    By nkumala in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-31-2016, 12:55 AM
  2. Employee Count per Interval
    By sarahj84 in forum Excel Formulas & Functions
    Replies: 34
    Last Post: 08-12-2016, 11:40 AM
  3. Employee Count Per Interval
    By sarahj84 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-11-2016, 12:10 PM
  4. How to summarize 30mins interval raw data directly to Monthly Data
    By dedark05 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2016, 01:38 AM
  5. Filter column data by time interval and count the number of records for each interval
    By erictamlam in forum Excel General
    Replies: 1
    Last Post: 04-29-2014, 04:42 AM
  6. [SOLVED] get a count depending on the interval
    By Frazzle6 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-19-2014, 09:09 PM
  7. [SOLVED] count the number of events in an interval
    By invictus in forum Excel General
    Replies: 4
    Last Post: 06-11-2012, 06:17 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1