Excel formula to tally numbers by month?

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

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

jent macrumors 6502a

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!

File size:
47.9 KB
Views:
167
File size:
21.5 KB
Views:
1,160
2. prisstratton macrumors 6502a

Joined:
Dec 20, 2011
Location:
#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.

Joined:
Jun 21, 2010
4. Pharmscott macrumors 6502a

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.

5. LongSticks macrumors 6502

Joined:
Jul 22, 2012
Location:
Kent, UK
#5
Yep pivot table definitely!

6. jent thread starter macrumors 6502a

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.

7. jent thread starter macrumors 6502a

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!

8. jlc1978 macrumors 68000

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.

9. Pharmscott macrumors 6502a

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.

File size:
87.8 KB
Views:
118
10. 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.

11. Pharmscott macrumors 6502a

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!

File size:
269.5 KB
Views:
203
12. Pharmscott macrumors 6502a

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.