Excel - Sum values in a column if corresponding date is in current month/quarter

[chucktizzle]

Looking for the correct formulas for cells B2, C2, and D2. They should sum the values in column H (Deal Size) if the corresponding date in column I falls within the specified range, i.e. current month, quarter, or year. Thanks!

[TABLE="class: grid, width: 1122"]
<colgroup><col><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Current Month Closed[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]Primary Contact[/TD]
[TD]Bank Name[/TD]
[TD]Opportunity[/TD]
[TD] Deal Size [/TD]
[TD]Close Date[/TD]
[/TR]
[TR]
[TD]Current Quarter Closed[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe[/TD]
[TD]First Bank of Atlantis[/TD]
[TD]20 FAL[/TD]
[TD] $ 19,900[/TD]
[TD="align: right"]11/5/2014[/TD]
[/TR]
[TR]
[TD]Current Year Closed[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane Doe[/TD]
[TD]Peoples Bank of Terabithia[/TD]
[TD]5 CLSB, CLSB SAL[/TD]
[TD] $ 14,950[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]Total Closed[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Current Open[/TD]
[TD] $ 14,950[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[vavs]

Chuck - I have a solution but it does involve adding some additional information. First, I added three columns to the right of the closed date. The first column is Month. The Cell J2 will have a formula: =IF((I2)= "Open","Open",MONTH(I2)) - copy this down for all rows. The column K will have the formula in K2 =IF((I2)= "Open","Open",Year(I2)) again copy down. Finally L will have the formula in L2 : =IF((J2)="Open", 0, IF(AND(J2<13,J2>9),4,IF(AND(J2<10,J2>6),3,IF(AND(J2<6,J2>3),2,1)))) and copy this down. Now we can start on the totals. In C2 you will put the formula: month(today()), in C3 put the formula: =IF(AND(C2<13,C2>9),4,IF(AND(C2<10,C2>6),3,IF(AND(C2<6,C2>3),2,1))) and finally in c4 the formula : year(today()).

Before you write the formula for summing the values, it is easiest to name the ranges. Name J:J Months, K:K Years, L:L Qtrs and most importantly H:H Dollars

Now the formulas become very simple

B2 formula: =sumif(Months,c2,Dollars)
B3 formula: =sumif(Qtrs,C3,Dollars)
B4 formula: = sumif(Years,C4,Dollars)
B5 formula: =SUMIF(Dollars,">0",Dollars)-SUMIF(Months,"Open",Dollars)
B6 formula: =Sumif(Months,"Open",Dollars)

The best way to do this in the future is a pivot table but this will work.

 

[RonB1111]

For current month closed:

=SUMIFS(H:H,I:I,"<="&EOMONTH(TODAY(),0),I:I,">"&EOMONTH(TODAY(),-1))

For current qtr closed:

=SUMIFS(H:H,I:I,"<="&DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+4,0),I:I,">="&DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1))

For current year closed:

=SUMIFS(H:H,I:I,"<="&DATE(YEAR(TODAY()),12,31),I:I,">="&DATE(YEAR(TODAY()),1,1))