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.

2. ## 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. ## 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

4. ## 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

5. ## 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.

6. ## 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

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

9. ## 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. ## 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

11. ## 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.)

12. ## Re: adding monthly totals

Forgot the attachments

13. ## 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. ## Re: adding monthly totals

Originally Posted by FDibbins
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. ## 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. ## Re: adding monthly totals

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

17. ## 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

18. ## Re: adding monthly totals

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

19. ## Re: adding monthly totals

Originally Posted by FDibbins
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.

20. ## Re: adding monthly totals

Originally Posted by FDibbins
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. ## Re: adding monthly totals

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

22. ## Re: adding monthly totals

Originally Posted by FDibbins
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. ## 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

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