Sumif with multiple criteria

[jn77]

I have a set of raw data that's huge. 4 columns are relevant for m
question: Quantity, Category, Style, Size. I need a formula tha
tells me the following:

Look for Category X and Style Y and Size Z. Give the sum of al
occurences in the Quantity column where X, Y and Z occu
simultaneously.

Is this possible? Thanks in advance for help

 

[MDubbelboer]

=SUMPRODUCT((x1:x999="category")*(y1:y999="style")*(z1:z999="size")*(w1:w999

assume 999 lines and that "category" is replaced by your criteria fo
category, etc
these criteria can also be references to cell

http://www.xldynamic.com/source/xld.SUMPRODUCT.html for more inf

 

[Dave]

jn77

=SUMPRODUCT(--((Category rng)=X),--((Style rng)=Y),--((Size rng)=Z),Quantity
rng)

replace rng with your range. Note all 4 ranges must be the same size.

Dave

 

[Bondi]

I have a set of raw data that's huge. 4 columns are relevant for my
question: Quantity, Category, Style, Size. I need a formula that
tells me the following:

Look for Category X and Style Y and Size Z. Give the sum of all
occurences in the Quantity column where X, Y and Z occur
simultaneously.

Is this possible? Thanks in advance for help!

Hi,

Maybe you can use SUMPRODUCT(). Something like:

=SUMPRODUCT(--(A1:A100="X"),--(B1:B100="Y"),--(C1:C100="Z"),D1100)

Where Category is in Column A, Style in B, Size in Z and Quantity in D.


Regards,
Bondi

 

[jn77]

First, thank you for your responses.
I've tried all the formulas each of you have offered, and none of the
work.
Does the data have to be organized in chronological order or anythin
like that like with vlookups?

Thanks

 

[MDubbelboer]

My coworker added a dsum formula.

i've included an example spreadsheet to show you how both are working

note that i2,j2,k2 are fully changeable and can be blank to include al

+-------------------------------------------------------------------
|Filename: example.zip
|Download: http://www.excelforum.com/attachment.php?postid=5057
+-------------------------------------------------------------------