Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formula required | Excel Worksheet Functions | |||
Turn 10106 in to 1st of JAN 2006, date wise I can't do it please h | Excel Discussion (Misc queries) | |||
Data row wise, formula column wise | Excel Discussion (Misc queries) | |||
Delete row wise duplicates & colomun wise simultaneously excel | Excel Worksheet Functions | |||
addition to my date formula...required | Excel Worksheet Functions |