Excel formula to tally numbers by month?

Discussion in 'Mac Apps and Mac App Store' started by jent, May 23, 2013.

  1. jent, May 23, 2013
    Last edited: May 23, 2013

    jent macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #1
    The awesome MacRumors Forums community taught me in this thread how to tally numbers when an adjacent cell in the matched a certain number using the SUMIF() function.

    In my included screenshots, you can see that I input the number of hours my team works on different projects with the date, and then the SUMIF() function conveniently gives me the total number of hours worked per project.

    I'm wondering how to extend this so that they can also be tallied by month. Because I often input project numbers and dates well after they've happened, they're not in chronological order and I'm constantly adding old information. Is there a way to have Excel break down the total number of hours worked per project per month?

    Let me know if I can provide any further information to clarify. Thanks for the help!
     

    Attached Files:

  2. prisstratton macrumors 6502

    prisstratton

    Joined:
    Dec 20, 2011
    Location:
    Winnipeg
    #2
    I have a solution for you using an array formula, kind of like a SUMIF with two conditions. I need to write up a few things first, but will PM you later.
     
  3. Pharmscott macrumors 6502a

    Pharmscott

    Joined:
    Dec 13, 2011
    Location:
    Sacramento, CA
    #4
    Seems like a job for a pivot table to me. Sums are basically built in; you just have to set the table properly.
     
  4. LongSticks macrumors 6502

    Joined:
    Jul 22, 2012
    Location:
    Kent, UK
    #5
    Yep pivot table definitely!
     
  5. jent thread starter macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #6
    What are the advantages of an array versus a PivotTable (or are they related?).

    I'll keep my eyes peeled for your sample spreadsheet, prisstratton.
     
  6. jent thread starter macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #7
    Thanks for the helpful replies, all. The user prisstratton was kind enough to send me a sample spreadsheet using an array, and it works great for what I need.

    Part of the spreadsheet includes a column that extracts the month of the date for each entry in order to tally the number of hours worked on each project per month. I'm wondering how I can achieve this over a multi-year period (months 1-36). I'm in the second year of a three-year project, so as it stands I am tallying work hours from multiple years.

    Thanks for any additional help!
     
  7. jlc1978 macrumors 68000

    jlc1978

    Joined:
    Aug 14, 2009
    #8
    If I understand what you are doing, if you extract the month, extract the year and make year 1=1, year 2=2, year 3 =3, multiply the year by the month and put the result in the column. You could use an if statement to do the math this way:

    Let's say year 1 is 2012 then, something like this:

    If(Year(A1)=2012,month(a1),if(Year(A1)=2013,month(a1)*2,if(Year(A1)=2014,month(a1)*3,0)))

    Should do it.

    This assumes month 1 is January. If not you need to subtract the start offset from each calc; I.e. if you start in June (month 6) then subtract 5 from each calculation to get 1-36 for the months. You will also need to add one more if statement to cover the final year.
    I can't remember if you need " around the calcs.
     
  8. Pharmscott macrumors 6502a

    Pharmscott

    Joined:
    Dec 13, 2011
    Location:
    Sacramento, CA
    #9
    I'm glad if you've found a solution. But, it seems more complicated than a simple pivot table. Using your template, I added a column that uses the Month function (since that's how you want to report). Then, I just pivoted the totals by month and work project number. Excel does the rest.

    The chart isn't fancy since I just tossed it together to show you the idea. Good luck! Post or PM if I can help.
     

    Attached Files:

  9. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #10
    In that case, it might be easier to add a column to separate out the year, and then add that as a condition. This is where PivotTables may prove more beneficial.
     
  10. Pharmscott macrumors 6502a

    Pharmscott

    Joined:
    Dec 13, 2011
    Location:
    Sacramento, CA
    #11
    If you're still checking this post, I took the pivot example to the next level for you. In this version, you can collapse and expand the month names to display the dates worked (Apr is expanded in the screen shot).

    I hope this helps!
     

    Attached Files:

  11. Pharmscott macrumors 6502a

    Pharmscott

    Joined:
    Dec 13, 2011
    Location:
    Sacramento, CA
    #12
    Hi jent-

    I tried PMing you but it doesn't seem to send so I'll post it here too.

    ---

    I'm glad you appreciated my post. Pivot tables can be powerful tools and are fairly easy to use in the latest version of Excel. Feel free to PM me with questions.
     

Share This Page