Excel formula to tally numbers by month?

[jent]

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!

 

[prisstratton]

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!

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.

 

[Qaanol]

Use SUMIFS

 

[Pharmscott]

Seems like a job for a pivot table to me. Sums are basically built in; you just have to set the table properly.

 

[LongSticks]

Seems like a job for a pivot table to me. Sums are basically built in; you just have to set the table properly.

Yep pivot table definitely!

 

[jent]

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.

 

[jent]

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!

 

[jlc1978]

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!

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.

 

[Pharmscott]

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.

 

[exegete77]

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.

 

[Pharmscott]

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!

 

[Pharmscott]

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!

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.