Date wise Sum Formula required

[Guest]

I have date, Qty code & Quantity.
08-03-2007 1 X 40 1
11-03-2007 2 X 20 2
11-03-2007 8 X 20 8
14-03-2007 1 X 20 1
30-03-2007 1 X 20 1
18-03-2007 1 X 20 1
18-03-2007 2 X 20 2
18-03-2007 1 X 20 1
21-03-2007 2 X 20 2

Note-The qty in the 3dr colm are got by simply placing the formula
=MID(A2:A$9,1,1) But I need formula to sum Qty date wise. And important is
that to avoid result repetition where the date repeats in some cells. Help me
please.

 

[T. Valko]

You should change your formula in the 3rd column to:

=--LEFT(B2,FIND(" ",B2)-1)

Copied down

To get the daily sum:

=IF(A2=A3,"",SUMIF(A$2:A$10,A2,C$2:C$10))

Copied down

Screencap:

http://img255.imageshack.us/img255/9885/sumifct3.jpg

Biff

 

[tqm1]

Now, Monthwise Total

How to get monthwise total in column C as below

----A--------B---C
08-03-2007--10--
11-03-2007--20--
11-03-2007--30--60
14-04-2007--40--
30-04-2007--50--
18-04-2007--60--150
18-05-2007--70--
18-05-2007--80--
21-05-2007--90--24

 

[T. Valko]

Try this:

=IF(AND(MONTH(A1)=MONTH(A2),ISNUMBER(A2)),"",SUMPRODUCT(--(MONTH(A$1:A$9)=MONTH(A1)),B$1:B$9))

copied down

Biff
Microsoft Excel MVP

 

[Guest]

That works great dear T. Valko. Thank you very much.

 

[T. Valko]

You're welcome. Thanks for the feedback!