+ Reply to Thread
Results 1 to 10 of 10

Make hourly summation of data

  1. #1
    Registered User
    Join Date
    04-18-2019
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    10

    Make hourly summation of data

    Capture.PNG

    In this excel file I already have the following formulas to make a daily and monthly summation;
    DAILY: =IF(DAY(A3)<>DAY(A4); SUMIFS(K:K;A:A; ">="&INT(A3);A:A; "<"&INT(A3)+1); "")
    MONTHLY: =IF(MONTH(A2)<>MONTH(A3); SUMIFS(K:K;A:A; ">="&EOMONTH(A2; -1)+1;A:A; "<"&EOMONTH(A2; 0)+1); "")

    I now want to make an hourly summation of the quarter-hour data that's given but just changing DAY or MONTH with HOUR does not work.

    Thanks for your help in advance.
    Last edited by NDS123; 04-18-2019 at 04:07 PM.

  2. #2
    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,369

    Re: Make hourly summation of data

    Welcome ot the forum!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    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.

  3. #3
    Registered User
    Join Date
    04-18-2019
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    10

    Re: Make hourly summation of data

    "Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen."

    When I try to upload the file it always fails. I tried uploading the .xlsx file of 4.377 MB and I also tried making a .zip file of 3.808 MB but this also failed to upload.

  4. #4
    Registered User
    Join Date
    04-18-2019
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    10

    Re: Make hourly summation of data

    "Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen."

    When I try to upload the file it always fails. I tried uploading the .xlsx file of 4.377 MB and I also tried making a .zip file of 3.808 MB but this also failed to upload.

  5. #5
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Make hourly summation of data

    Max file size for a zip file is almost 10 MB, so that shouldn't be the issue. However, can you try trimming down your data to a smaller subset so the xlsx file is smaller? Max upload size for xlsx is 1000 KB.

  6. #6
    Registered User
    Join Date
    04-18-2019
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    10

    Re: Make hourly summation of data

    I think this did the trick. I removed all months except January so the total file size is around 900 KB.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Make hourly summation of data

    What column do you want the hourly summation in?

  8. #8
    Registered User
    Join Date
    04-18-2019
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    10

    Re: Make hourly summation of data

    You can pick an empty column free of your choice! Column P, U or V is an option!

  9. #9
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Make hourly summation of data

    This isn't 100% tested, but see if this works for you:
    Please Login or Register  to view this content.
    Enter in a cell on row 2 and drag down. Does that give you what you're looking for?
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  10. #10
    Registered User
    Join Date
    04-18-2019
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    10

    Re: Make hourly summation of data

    Yep, this works. Thank you very much, Sir!

+ 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. [SOLVED] Converting half hourly data to hourly data
    By jmasanganise in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-10-2020, 08:51 PM
  2. [SOLVED] I want to make a summation of all values that equal my Vlookup value.
    By guillaume25 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2018, 05:26 AM
  3. Please help me figure out how to make this summation work
    By egoldin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2017, 06:20 PM
  4. How to choose hourly data to make 24 data set daily
    By arunjyoti.ec in forum Excel General
    Replies: 7
    Last Post: 07-26-2015, 06:10 AM
  5. How do I add a number from each Hourly data to 1/4 hourly about 8,000 times?
    By Louis59 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-26-2014, 01:02 AM
  6. How to make the summation to zero?
    By lubbamkt in forum Excel General
    Replies: 3
    Last Post: 06-28-2014, 01:39 PM
  7. How to make a positive summation show red
    By John De Beloved Abah in forum Excel General
    Replies: 1
    Last Post: 02-24-2012, 09:09 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