Monthly Totals

I'm sure if you are a regular at the excel forums you are a bit peeved by the title of this post, I am truly sorry that i could not locate the answer myself because I'm sure it's been answered. ANYWAYS....

I am trying to get monthly totals at the end of a excel sheet. I am "self taught" using excel (and I'm not too good) and so far just from playing around with it my best solution is this:

I am using closing dates of properties that I sell (I'm a Realtor)
The closing date column is D
I need to add the sold price for each month and commission amount for each month, which are columns E and F respectively.
I made a column (H) with the formula [ =MONTH(d1), =Month(d2), =Month(d3), etc. ]
I made a formula for the bottom of the sheet for the sold price totals for January that is [ =SUMIF(H2:H40,1,E2:E40) ]
then for February that is [ =SUMIF(H2:H40,2,E2:E40) ]

Then I do basically the same thing to add up the commission values in column F

It works.... However, there has to be a simpler way, right!? Also, I can't stand the H column just sitting there with a single digit in it. Is there a way to nest if statements to just pull the month number right out of the date column D???

Thanks so much in advance to listening to my rambling.... picture included to clarify anything that i jumbled up....
Untitled.png

Originally Posted by Glenn Kennedy

You can always hide the helper column...

You are also making life a bit more awkward for yourself by having your summary at the foot of the data table. You'd be able to use simpler formulae if it was at the top, or on another sheet altogether. The EOMONTH function is also handy to avoid using the helper column. However, since you've attached a jpg, it makes showing you rather more difficult.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

Well luckily, it is ALL public information, so no need to remove anything (it's good for me so I don't get confused if you say something like replace "Baby Ducks" in the example with "Monthly totals"... or something to that effect)

and like i said, I am self taught. while putting these values on a different sheet or in a different place makes perfect sense to me, i just dont know exactly where is best... I tend to like everything on one screen when possible but it's definitely not necessary for my applications

Here's the file:
Closed 2015.xlsx

Originally Posted by bhenlee

try this formula:

=SUMIFS($$E2:$E$40,$D$46:$D$53,">="&DATEVALUE($D46&1),$D$46:$D$53,"<="&DATEVALUE($D47&1))

I apologize if I am missing something, I tried adding this formula into the sheet and it came up with a #VALUE! ....

but, maybe I can follow with a little more explanation. What do the $ in the formula represent? I've been wondering that... I probably should take a class or something lol, but I prefer to just rely on the wonderful free trade of ideas on the internet....

Originally Posted by Glenn Kennedy

I'd move the summary to the top, put months as 01/01/2015, formatted as mmmm to look like January, starting in D2 down to December in D13 and use this formula:

=IF(SUMIFS(E$21:E$301,$D$21:$D$301,">="&$D2,$D$21:$D$301,"<="&EOMONTH($D2,0))=0,"",SUMIFS(E$21:E$301,$D$21:$D$301,">="&$D2,$D$21:$D$301,"<="&EOMONTH($D2,0)))

copied across and down.

See attached.

Okay, I see that it works, but.... (and maybe i'm just being greedy) I want to know WHY and HOW it works. I can probably do this on my own by breaking it apart and trying to recreate it in different sheets. Thank you so much for your quick reply, and while I hate the monthly totals at the top (I just do, i don't know why) I see how it can be benificial (ie incorporating all future values in certain columns into the formulas etc)

again, thank you thank you thank you! I think you just created a monster, i'm going to be on this forum until all of my spreadsheets are flawlessly awesome. Now teaching my GF (Co-worker) these things will be the true test!

Excellent! I will mark this solved and distribute appropriate kudos accordingly! Thanks again to everyone who gave their input!

what are the $'s ??? Are they to "extract" the value from the cell?