If Question...

[JeremyH1982]

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

 

[Bernard Liengme]

=SUMPRODUCT(--(D110=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

 

[Luke M]

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")*(D1100=1.5)*(F1:F100))
Gives you qty of Alexis that is width N and size 1.5

 

[JeremyH1982]

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