Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Question...
hey everyone,
I'm in need of a formula, I'm thinking an IF formula... In Column D i have sizes, such as 1; 1.5; 2; 2.5 etc... in Column E, I have Width, N; M; W; XW, and in Column F I have a #, basically quantity sold... Now it's sorted by Style Name, then by Color, Then by Width, and the Size is automatically put together based on #. What i want to know, How many 1.5 M were sold or how many 2 M or 2 W for any given style, which the styles are listed by Name not by Number... For Example, Rows 1-5 have the Style listed as Alexis, for all 5 rows, Column D is 1 (correlating that size 1 was sold to 5 customers, 1 row per customer) in Column E the width is M (again, 5 customers bought size 1 M style Alexis) Then in Column F is the pairs they bought. I want that answer to be in 1 column or in 1 place rather than 5 rows... Is there a better way than the IF statement, and if so, what would the answer be?? -- Jeremy Himmelreich Computer IT Manager http://home.comcast.net/~jeremy1982 http://www.willitshoe.com |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Question...
=SUMPRODUCT(--(D1:D10=1.5),--(E1:E100="M"),F1:F100)
For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JeremyH1982" wrote in message ... hey everyone, I'm in need of a formula, I'm thinking an IF formula... In Column D i have sizes, such as 1; 1.5; 2; 2.5 etc... in Column E, I have Width, N; M; W; XW, and in Column F I have a #, basically quantity sold... Now it's sorted by Style Name, then by Color, Then by Width, and the Size is automatically put together based on #. What i want to know, How many 1.5 M were sold or how many 2 M or 2 W for any given style, which the styles are listed by Name not by Number... For Example, Rows 1-5 have the Style listed as Alexis, for all 5 rows, Column D is 1 (correlating that size 1 was sold to 5 customers, 1 row per customer) in Column E the width is M (again, 5 customers bought size 1 M style Alexis) Then in Column F is the pairs they bought. I want that answer to be in 1 column or in 1 place rather than 5 rows... Is there a better way than the IF statement, and if so, what would the answer be?? -- Jeremy Himmelreich Computer IT Manager http://home.comcast.net/~jeremy1982 http://www.willitshoe.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Question...
First idea would be to use a PivotTable (Data-PivotTable)
Set it up to display "Styles", "Sizes", and Widths as your rows, (or columns, arrange to your preference) and quantity as your data. If you really want a formula, you'll need to tinker with the SUMPRODUCT formula, e.g., =SUMPRODUCT((A1:A100="Alexis")*(E1:E100="N")*(D1:D 100=1.5)*(F1:F100)) Gives you qty of Alexis that is width N and size 1.5 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JeremyH1982" wrote: hey everyone, I'm in need of a formula, I'm thinking an IF formula... In Column D i have sizes, such as 1; 1.5; 2; 2.5 etc... in Column E, I have Width, N; M; W; XW, and in Column F I have a #, basically quantity sold... Now it's sorted by Style Name, then by Color, Then by Width, and the Size is automatically put together based on #. What i want to know, How many 1.5 M were sold or how many 2 M or 2 W for any given style, which the styles are listed by Name not by Number... For Example, Rows 1-5 have the Style listed as Alexis, for all 5 rows, Column D is 1 (correlating that size 1 was sold to 5 customers, 1 row per customer) in Column E the width is M (again, 5 customers bought size 1 M style Alexis) Then in Column F is the pairs they bought. I want that answer to be in 1 column or in 1 place rather than 5 rows... Is there a better way than the IF statement, and if so, what would the answer be?? -- Jeremy Himmelreich Computer IT Manager http://home.comcast.net/~jeremy1982 http://www.willitshoe.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Question...
yes, PivotTable works...
I just have to manipulate the data the way I want it displayed... I'm not very familiar with PivotTables, but I'm getting the hang of them. Thanks -- Jeremy Himmelreich Computer IT Manager http://home.comcast.net/~jeremy1982 http://www.willitshoe.com "Luke M" wrote: First idea would be to use a PivotTable (Data-PivotTable) Set it up to display "Styles", "Sizes", and Widths as your rows, (or columns, arrange to your preference) and quantity as your data. If you really want a formula, you'll need to tinker with the SUMPRODUCT formula, e.g., =SUMPRODUCT((A1:A100="Alexis")*(E1:E100="N")*(D1:D 100=1.5)*(F1:F100)) Gives you qty of Alexis that is width N and size 1.5 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JeremyH1982" wrote: hey everyone, I'm in need of a formula, I'm thinking an IF formula... In Column D i have sizes, such as 1; 1.5; 2; 2.5 etc... in Column E, I have Width, N; M; W; XW, and in Column F I have a #, basically quantity sold... Now it's sorted by Style Name, then by Color, Then by Width, and the Size is automatically put together based on #. What i want to know, How many 1.5 M were sold or how many 2 M or 2 W for any given style, which the styles are listed by Name not by Number... For Example, Rows 1-5 have the Style listed as Alexis, for all 5 rows, Column D is 1 (correlating that size 1 was sold to 5 customers, 1 row per customer) in Column E the width is M (again, 5 customers bought size 1 M style Alexis) Then in Column F is the pairs they bought. I want that answer to be in 1 column or in 1 place rather than 5 rows... Is there a better way than the IF statement, and if so, what would the answer be?? -- Jeremy Himmelreich Computer IT Manager http://home.comcast.net/~jeremy1982 http://www.willitshoe.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If,And Question | Excel Discussion (Misc queries) | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
< or question | Excel Worksheet Functions | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions |