Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding cells based on another cell
I have a list that is probably 20 rows long.
The list is 4 columns wide. The columns are Dealer #, Quote #, Quantity, and Status. Status is a data validation list box with 3 different options (Pending, Won, Lost). I need to have 3 different totals, one for each of the options. So I want a total of the quantities for each "Pending", "Won", "Lost". Please advise, Let me if you need any other information. Thanks Travis. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding cells based on another cell
Hi Travis,
You could use something like this... =SUMIF(D2:D22,"Pending",C2:C22) This will sum the total quantity of status Pending Assuming Status range is D2:D22 and Quantity is C2:C22 Hope this helps, Gav. "Tdahlman" wrote: I have a list that is probably 20 rows long. The list is 4 columns wide. The columns are Dealer #, Quote #, Quantity, and Status. Status is a data validation list box with 3 different options (Pending, Won, Lost). I need to have 3 different totals, one for each of the options. So I want a total of the quantities for each "Pending", "Won", "Lost". Please advise, Let me if you need any other information. Thanks Travis. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding cells based on another cell
Assuming Quantity is in column C and Status is in column D, use these formulas:
=SUMPRODUCT(C1:C100,--(D1:D100="pending")) =SUMPRODUCT(C1:C100,--(D1:D100="won")) =SUMPRODUCT(C1:C100,--(D1:D100="lost")) -- Gary''s Student - gsnu200771 "Tdahlman" wrote: I have a list that is probably 20 rows long. The list is 4 columns wide. The columns are Dealer #, Quote #, Quantity, and Status. Status is a data validation list box with 3 different options (Pending, Won, Lost). I need to have 3 different totals, one for each of the options. So I want a total of the quantities for each "Pending", "Won", "Lost". Please advise, Let me if you need any other information. Thanks Travis. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding cells based on another cell
Try this:
=SUMIF(D1:D100,"Pending",C1:C100) HTH, Paul -- "Tdahlman" wrote in message ... I have a list that is probably 20 rows long. The list is 4 columns wide. The columns are Dealer #, Quote #, Quantity, and Status. Status is a data validation list box with 3 different options (Pending, Won, Lost). I need to have 3 different totals, one for each of the options. So I want a total of the quantities for each "Pending", "Won", "Lost". Please advise, Let me if you need any other information. Thanks Travis. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding cells based on another cell
Are you sure that's correct? I had to use:
=SUMPRODUCT(--(D1:D100="Pending"),C1:C100) -- "Gary''s Student" wrote in message ... Assuming Quantity is in column C and Status is in column D, use these formulas: =SUMPRODUCT(C1:C100,--(D1:D100="pending")) =SUMPRODUCT(C1:C100,--(D1:D100="won")) =SUMPRODUCT(C1:C100,--(D1:D100="lost")) -- Gary''s Student - gsnu200771 "Tdahlman" wrote: I have a list that is probably 20 rows long. The list is 4 columns wide. The columns are Dealer #, Quote #, Quantity, and Status. Status is a data validation list box with 3 different options (Pending, Won, Lost). I need to have 3 different totals, one for each of the options. So I want a total of the quantities for each "Pending", "Won", "Lost". Please advise, Let me if you need any other information. Thanks Travis. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding numbers in cells based on colour | Excel Worksheet Functions | |||
adding specific cells based on a corresponding date | Excel Worksheet Functions | |||
adding color to cells based on a formula | Excel Worksheet Functions | |||
Adding cells based on condition | Excel Worksheet Functions | |||
Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) |