+ Reply to Thread
Results 1 to 23 of 23

adding monthly totals

  1. #1
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    adding monthly totals

    Column B of my spreadsheet has the number of miles I run each day. Column A of my worksheet as the dates in the year I have run. So A1 might be 1/1/17, A2 would be 1/2/17 if I ran on 1/2. Otherwise, A2 would be the next day I ran - say 1/4/17.


    To the side I have an other columns January - December. Next to each month I know have a blank cell. I want the number of miles I have run each month next to the name of the month. Is there a formula I could use for each month? I just can’t do an adding of all columns A1-A31 (for January) as I do not run each day. A31 would probably give me a date around Feb 10th.

    TIA.
    Last edited by allan473; 03-22-2017 at 01:34 PM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: adding monthly totals

    Simple enough to do but the solution will very much depend on the format of the month cells. Are they January as text or 01/01/17 formatted to show as just January?

    Could you post a sample workbook?

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    BSB

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: adding monthly totals

    Hi allan and welcome to the forum,

    Excel has Pivot Tables that can do this problem easily. See the attached where I've created fake data and done a Pivot Table of it. I think this is what you want. Start learning Pivot Tables??
    Pivot Table Running Log by Month.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: adding monthly totals

    Hi,

    A SUM(IF array formula will do the job for you.

    This formula was written for the attached example file:

    Please Login or Register  to view this content.
    It is an array formula that is entered with a CTRL/SHIFT/ENTER.

    Hope this works for you.

    Cheers
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    Here is the sample sheet. The only changes from what I said above are that dates in column A will start at a14 and go down to around A379 if I run each day or a150 if I run less (I can't say how many days I will run) and miles are in column e.
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: adding monthly totals

    If you click on 1 of your dates on the top table, you will see that are all for the year 1999. Change those to the year you want, then use this...
    =SUMIFS($E:$E,$A:$A,">="&E4,$A:$A,"<"&EDATE(E4,1))
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: adding monthly totals

    Hey allan,

    I've removed your Merged cells as Excel Experts hate them. Then, using NO formulas and in about 6 mouse clicks, did a Pivot Table like I think you want.

    Pivot Table Running Log by Month 2xlsx.xlsx

  8. #8
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    Thanks for all the help. I have tried these suggestions and just get lost. The closest I got was trying to do what southward suggested. d but when I move his rod.
    If anyone wants to help with this sheet I need formulas to get data into d5 through d10 and f5 through f10.
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: adding monthly totals

    As I pointed out to you, the YEARS in the top table B5:E10 are all 1999 - you need to change them top THIS year, then my suggestion - and probably the others, will work for you

  10. #10
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: adding monthly totals

    Here is a formula for D5 and F5:

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    They are array formulas that have to be entered with CTRL/SHIFT/ENTER

    Unfortunately array formulas won't work in a merged cell. You will have to modify your form.

    Attached is a modified version of your form.

    Cheers
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    southward,
    Thanks for the worksheet. It works well as is. However, when I copy the sheet and pasted it into my running log which has several sheets the formulas you gave me no longer work

    This worked in your sheet for column d

    =SUM(IF(MONTH($A$15:$A$580)=MONTH(B5),$E$15:$E$580,0))

    A is date, 15 is the row where the first date (1/1/17) begins
    b5 is where the first month is January and E is the column where miles are entered.

    I entered your formula into c5 which is where the totals for January will go. I pressed enter and get what is in the second capture. I then entered shift-control-enter and the same thing happened. I am at a loss.

    Attached is a screen capture plus I have also included my entire running log with the problem area on the first sheet - log.

    Oh, I have Excel for Mac 2011 (if that matters.)
    Last edited by allan473; 03-23-2017 at 12:54 PM.

  12. #12
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    Forgot the attachments
    Attached Images Attached Images
    Attached Files Attached Files

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: adding monthly totals

    did you change the years in that top table yet?

    Unless you do that, the best you will get are workarounds to try and fix that mistake

    In fact, do you even know what Im talking about with those dates?

  14. #14
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    Quote Originally Posted by FDibbins View Post
    did you change the years in that top table yet?

    Unless you do that, the best you will get are workarounds to try and fix that mistake

    In fact, do you even know what Im talking about with those dates?
    Yes, I changed the dates to 2017.

  15. #15
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: adding monthly totals

    OK. The first suggestion I provided called for a formula that reference a month cell in the form of the month name.
    Then you provided your example file that had a month cell in the form of a date value. The formula I then provided was for a date value.
    Now you provide another example file with a month cell in the form of a month name.

    Yes, the value behind what is displayed in the cell makes a difference. While 01/01/1999 or 01/01/2017 can be made to show as Jan. The text Jan is totally different and calls for a different kind of formula.

    Here is the array formula for C5:

    Please Login or Register  to view this content.
    Is array formula that has to be entered with CTRL/SHIFT/ENTER

    Cheers

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: adding monthly totals

    Quote Originally Posted by allan473 View Post
    Yes, I changed the dates to 2017.
    Can you upload the corrected file, and show my formula not working please?

  17. #17
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    OK, I understand the date formats Jan vs 1/1/17 now.

    I entered the formula in C5 and it works. I then dragged down to get months Jan - June all working fine.

    July - December are in cells e5 - e10. I copied your formula, changed the B5 to E5, and expected it to work. Instead I got an error
    Attached Images Attached Images

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: adding monthly totals

    Upload a sample file, not a pic please, we cant work with pics

  19. #19
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    Quote Originally Posted by FDibbins View Post
    Can you upload the corrected file, and show my formula not working please?

    Formula is working for months Jan - June but not for July - Dec.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    Quote Originally Posted by FDibbins View Post
    Upload a sample file, not a pic please, we cant work with pics
    I am close - can not show last 6 months of the year. The rest is fine. I just uploaded the file.

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: adding monthly totals

    That was not my formula, I suggested this...
    =SUMIFS($E:$E,$A:$A,">="&B5,$A:$A,"<"&EDATE(B5,1))

  22. #22
    Registered User
    Join Date
    03-22-2017
    Location
    New Hampshire
    MS-Off Ver
    Mac 16.48
    Posts
    32

    Re: adding monthly totals

    Quote Originally Posted by FDibbins View Post
    That was not my formula, I suggested this...
    =SUMIFS($E:$E,$A:$A,">="&B5,$A:$A,"<"&EDATE(B5,1))
    FDibbins,
    I was getting confused - I loved the fact that several people were helping me but with different types of formulas I was lost.

    Now, I just used your formula and all is fine. Thanks a lot for your time!!!

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: adding monthly totals

    Yes, I can understand how you could get mixed up, no problem.

    Im happy to help and thanks for the feedback

+ 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: 1
    Last Post: 08-15-2013, 02:15 PM
  2. [SOLVED] How do I group daily totals into weekly/monthly/quarterly totals
    By situationroom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2013, 09:58 AM
  3. setting up a workbook for adding totals together for a week and monthly basis
    By jafooli in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2012, 07:21 AM
  4. Monthly Totals
    By gunmetal in forum Excel General
    Replies: 0
    Last Post: 08-12-2010, 10:12 AM
  5. [SOLVED] Summing Weekly Totals into Monthly Totals
    By steph44haf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-05-2006, 11:55 AM
  6. [SOLVED] Monthly Totals
    By Jasmine in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-27-2005, 05:05 PM
  7. How do I sum YTD totals based on monthly totals
    By Bsgrad02 in forum Excel General
    Replies: 3
    Last Post: 07-12-2005, 12:05 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