Totalling and Tallying Sales by Month

[aprilkindarocks]

[FONT=&quot]I'm creating a tracking document for the real estate team I work with. I'm in way over my head in excel skills (this is news to no one on the team, we just realized it's cheaper for me to spend the hours to make this document and have it as our own rather than pay for the one we've been using, and now we can, haha, customize to track exactly what we want - I'm proposing that the increase in my general wine intake necessary to deal with this project be categorized a business expense).[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Anyways, check out this screenshot:
https://www.dropbox.com/s/zsycd015z5nep04/Screenshot 2018-12-06 16.09.17.png?dl=0[/FONT]
[FONT=&quot]I want to track each month's closed sales units, closed sales volume, and closed commission (CGI). Apparently this must be done with some wizardry involving the COUNTIF function and date tools that are, again, way over my head. I'm probably missing an eye of newt or tail of mortgage lender somewhere and I'm sick of blowing up the lab with near misses. Help please?[/FONT]

 

[RasGhul]

Hi,

If you use a setup like this you can select which calendar year you need and the Month headings will update for you and the Countifs & sumifs with them.

Use Custom Formatting to make real dates look like Months only with mmmm; Note you will need to be careful with column H & I as they are merged on your sheet so I've attached a copy of my mock up.

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	1/01/2019	1/02/2019	1/03/2019	April		May	June	July	August	September	October	November	December	Criteria	Select Year
2	2													Closed	1/01/2019
3	$575,700.00
4	$17,271.00
5
6
7											Close Date
8
9
10	Closed										2/01/2019		$500,000.00			$15,000.00
11
12
13
14
15
16	Closed										4/01/2019		$75,700.00			$2,271.00
Sheet1

Cell Formulas
Range	Formula
J1	=EOMONTH($E$1,COLUMNS($F$1:J1)-2)+1
E1	=DATE(YEAR(S2),1,1)
E2	=COUNTIFS($E$10:$E$16,$R$2,$O$10:$O$16,">="&E$1,$O$10:$O$16,"<="&EOMONTH(E$1,0))
E3	=SUMIFS($Q$10:$Q$16,$E$10:$E$16,$R$2,$O$10:$O$16,">="&E$1,$O$10:$O$16,"<="&EOMONTH(E$1,0))
E4	=SUMIFS($T$10:$T$16,$E$10:$E$16,$R$2,$O$10:$O$16,">="&E$1,$O$10:$O$16,"<="&EOMONTH(E$1,0))
F1	=EOMONTH($E$1,COLUMNS($F$1:F1)-1)+1
G1	=EOMONTH($E$1,COLUMNS($F$1:G1)-1)+1
H1	=EOMONTH($E$1,COLUMNS($F$1:H1)-1)+1

https://www.dropbox.com/s/p0oky62vlvycwnc/Totals_tallys_month.xlsx?dl=0