Sumif with multiple criteria

J

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
 
D

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
 
B

Bondi

jn77 said:
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!
Click to expand...

Hi,

Maybe you can use SUMPRODUCT(). Something like:

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

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


Regards,
Bondi
 
J

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
 
M

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
+-------------------------------------------------------------------
 
Top