Date wise Sum Formula required
 

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.

Date wise Sum Formula required
 

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

"Narnimar" wrote in message
...
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.

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--240

Date wise Sum Formula required
 

Try this:

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

copied down

Biff
Microsoft Excel MVP

"tqm1" wrote in message
...

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--240




--
tqm1

Date wise Sum Formula required
 

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

"T. Valko" wrote:

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

"Narnimar" wrote in message
...
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.

Date wise Sum Formula required
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
That works great dear T. Valko. Thank you very much.

"T. Valko" wrote:

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

"Narnimar" wrote in message
...
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.