# Excel formula to tally numbers by month?

#### jent

##### macrumors 6502a
Original poster
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!

#### Attachments

• 47.9 KB Views: 204
• 21.5 KB Views: 1,196
Last edited:

#### prisstratton

##### macrumors 6502a
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.

Use SUMIFS

#### Pharmscott

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

#### LongSticks

##### macrumors 6502
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

##### macrumors 6502a
Original poster
What are the advantages of an array versus a PivotTable (or are they related?).

#### jent

##### macrumors 6502a
Original poster
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.

#### jlc1978

##### macrumors 68020
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.

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

##### macrumors 6502a
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.

#### Attachments

• 87.8 KB Views: 144

#### exegete77

##### macrumors 6502a
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

##### macrumors 6502a
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!

#### Attachments

• 269.5 KB Views: 216

#### Pharmscott

##### macrumors 6502a
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.