Need Help 2.
 

Hi, I need help.

Question 1. I want to have a formula that can automatically sum the amount
in columm 'D when date in columm 'C' meet my requirement. Normally, I just
want to know the total qty in particular month. Please refer to table below.
What formula I need to have to return the total qty based on different Part
Name and different Month? Eg. I want to know the total qty of Screw AB
required for the month of Jan. Columm 'A' contains more than 1,500 different
part name.

Question 2. A formula to return the furthest delivery date per a
particular part. Eg. I want the furthest delivery date for Screw AB, the
answer is 6-Feb-07.

Normally I have to do calculation by using sum, drag manually.... more than
1,500 parts.... Weekly... more chances to human error..


A B C D
1 Part name stock Delivery date PO QTY
2 Screw AB 55,000 1-Jan-07 12,000
3 2-Jan-07 10,000
4 3-Jan-07 10,000
5 4-Feb-07 10,000
6 5-Feb-07 10,000
7 6-Feb-07 10,000
8 Screw MN 60,000 1-Jan-07 12,000
9 2-Jan-07 10,000
10 3-Jan-07 10,000
11 4-Feb-07 10,000
12 5-Feb-07 10,000
13 6-Feb-07 10,000


Thanks in advance..
Sara

Need Help 2.
 

For Q1 =SUMPRODUCT((MONTH(C1:C100)=1)*(D1:D100)

For Q2, shame you data is not set out as a proper data base (a column with
item code)

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sara" wrote in message
...
Hi, I need help.

Question 1. I want to have a formula that can automatically sum the
amount
in columm 'D when date in columm 'C' meet my requirement. Normally, I just
want to know the total qty in particular month. Please refer to table
below.
What formula I need to have to return the total qty based on different
Part
Name and different Month? Eg. I want to know the total qty of Screw AB
required for the month of Jan. Columm 'A' contains more than 1,500
different
part name.

Question 2. A formula to return the furthest delivery date per a
particular part. Eg. I want the furthest delivery date for Screw AB, the
answer is 6-Feb-07.

Normally I have to do calculation by using sum, drag manually.... more
than
1,500 parts.... Weekly... more chances to human error..


A B C D
1 Part name stock Delivery date PO QTY
2 Screw AB 55,000 1-Jan-07 12,000
3 2-Jan-07 10,000
4 3-Jan-07 10,000
5 4-Feb-07 10,000
6 5-Feb-07 10,000
7 6-Feb-07 10,000
8 Screw MN 60,000 1-Jan-07 12,000
9 2-Jan-07 10,000
10 3-Jan-07 10,000
11 4-Feb-07 10,000
12 5-Feb-07 10,000
13 6-Feb-07 10,000


Thanks in advance..
Sara

Need Help 2.
 

Hi Sara

Bernard has given you the answer for part 1.

Before starting the following, ensure you have a backup copy of your
data - just in case!!!
To turn the data into a "proper" data base, in E2 enter the following
=IF(A2="",A1,A2)
Copy down for the extent of your data.
Copy column E and Paste SpecialValues back over column A
Delete column E
When entering new data, always include the part number in column A

Now to mind your second answer use the array entered formula
{=MAX((A2:A1500="Screw AB")*(C2:C1500)}
Adjust ranges to deal with the extent of your data.

To enter or amend an array formula, commit with control + Shift + Enter
(CSE), not just Enter.
When you use CSE, Excel will insert the curly braces { } for you. Do
not type them yourself.

It would be better to use a spare cell e.g. F1 to enter the part number,
then the same formula can be used regardless of which part is required,
merely by changing the value in F1
{=MAX((A2:A1500=F1)*(C2:C1500)}

--
Regards

Roger Govier


"Sara" wrote in message
...
Hi, I need help.

Question 1. I want to have a formula that can automatically sum the
amount
in columm 'D when date in columm 'C' meet my requirement. Normally, I
just
want to know the total qty in particular month. Please refer to table
below.
What formula I need to have to return the total qty based on different
Part
Name and different Month? Eg. I want to know the total qty of Screw AB
required for the month of Jan. Columm 'A' contains more than 1,500
different
part name.

Question 2. A formula to return the furthest delivery date per a
particular part. Eg. I want the furthest delivery date for Screw AB,
the
answer is 6-Feb-07.

Normally I have to do calculation by using sum, drag manually.... more
than
1,500 parts.... Weekly... more chances to human error..


A B C D
1 Part name stock Delivery date PO QTY
2 Screw AB 55,000 1-Jan-07 12,000
3 2-Jan-07 10,000
4 3-Jan-07 10,000
5 4-Feb-07 10,000
6 5-Feb-07 10,000
7 6-Feb-07 10,000
8 Screw MN 60,000 1-Jan-07 12,000
9 2-Jan-07 10,000
10 3-Jan-07 10,000
11 4-Feb-07 10,000
12 5-Feb-07 10,000
13 6-Feb-07 10,000


Thanks in advance..
Sara

Need Help 2.
 

Hi, guys, Thanks a lot !

Wish you a good day!