Monthwise calclulation

Could u please help me in solving these belwo two queries:

1. Monthwise total sales
2. Monthwise average sales

File is attached herewith for your persual please.

Thanks
Vijay

Hi

I used the month table you have at the side for the sum and the average per month.

In I4, copied down...
=SUMIFS($C$4:$C$93,$A$4:$A$93,">="&H4,$A$4:$A$93,"<"&H5)
In J4, copied down...
=IFERROR(AVERAGEIFS($C$4:$C$93,$A$4:$A$93,">="&H4,$A$4:$A$93,"<"&H5),"")

Note...make sure all the dates in H begin with the 1st of the month. you have some that start on the 12th etc

sorry total isnt coming 111 for jan12 month

Originally Posted by FDibbins

Note...make sure all the dates in H begin with the 1st of the month. you have some that start on the 12th etc

your date for Jan is not 1/1/2012, it is 1/11/2012 and 2/12/2012

yes its sales which can be on any day...
I mean to say sir on there may be some reason where on 1st day.. there would be no sale

Try in C1 (total sales)

=SUMPRODUCT(--(MONTH($A$4:$A$93)=MONTH($B$2)),(C4:C93))

Could u also help me to make pivot chart of that
in Axia column i neet month and in value i need sum of sale of Brakes , clutch and seat cover.

Hi Vijay,

See the attached file where I used below formulas:-
for total:-

Formula:

=SUMPRODUCT((MONTH($A$4:$A$93)=MONTH(G4))*($C$4:$E$93))

for average:-

Formula:

=H4/(EOMONTH(G4,0)-(EOMONTH(G4,-1)))

or,

Formula:

=IF(H4=0,0,$H4/SUMPRODUCT((MONTH($A$4:$A$93)=MONTH($G4))*1))

MonthlyAveryagesale.xlsx


Regards,
DILIPandey
<click on below * if this helps>

Thanks its done
and if I want to show this data in Pivot chart monthwise then how it can be ?

Because you are looking only at Clutch... dont you want to consider Brake wires and seat cover ?




Regards,
DILIPandey
<click on below * if this helps>

Hi Vijay,

I believe we are done with this thread as title refers to some " calculation "... if you need support on chart and other things, suggest you to post new thread after clubbing related items.. Thanks.


Regards,
DILIPandey
<click on below * if this helps>