Formula to find totals by Quarter from monthly sales

[bjnockle]

I have month in column A and sales values in column B. Looking for a formula to find totals by Quarter from a set of monthly sales values in B. The aim is to have the formula sum the sales value when all three months values are available. For example, Jan -10, $10, Feb-10, $20 and mar-10, $30. Quarter 1 sales value will be $60.00. If Apr-10, $10, May-10, $10 and Jun-10 value is not yet entered, the formula should return blank until the jun-10 sales value is entered.

Sample workbook attached.

Thanks for the help.

[Pepe Le Mokko]

Perhaps use a Pivot Table where you filter out the incomplete quarter?

[samba_ravi]

=SUMPRODUCT((MONTH($A$2:$A$13)>=IF(D3="Q1",1,IF(D3="Q2",4,IF(D3="Q3",7,10))))*(MONTH($A$2:$A$13)<=IF(D3="Q1",3,IF(D3="Q2",6,IF(D3="Q3",9,12))))*($B$2:$B$13))
try this formula

[Pete_UK]

Put this formula in C2:

=IF(B2="","","Q"&INT((MONTH(A2)-1)/3)+1)

then copy down to C13.

Then put this formula in E3:

=IF(COUNTIF(C$2:C$13,D3)=3,SUMIF(C$2:C$13,D3,B$2:B$13),"")

and copy down to E6.

Hope this helps.

Pete

[Chris 53]

In E3 try:-
=IF(COUNTA(B2:B4)=3, SUM(B2:B4),"")

Adjust the formulas for E4 to E6

Is this what you are looking for?

Chris

[bjnockle]

Pete_UK: The proposed formula works like a charm! Thanks for assisting.