Sumif with multiple criteria
 

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!


--
jn77
------------------------------------------------------------------------
jn77's Profile: http://www.excelforum.com/member.php...o&userid=36547
View this thread: http://www.excelforum.com/showthread...hreadid=563019

Sumif with multiple criteria
 

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

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

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


--
MDubbelboer
------------------------------------------------------------------------
MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
View this thread: http://www.excelforum.com/showthread...hreadid=563019

Sumif with multiple criteria
 

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

"jn77" wrote in message
...

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!


--
jn77
------------------------------------------------------------------------
jn77's Profile:
http://www.excelforum.com/member.php...o&userid=36547
View this thread: http://www.excelforum.com/showthread...hreadid=563019

Sumif with multiple criteria
 

jn77 wrote:
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!


--
jn77
------------------------------------------------------------------------
jn77's Profile: http://www.excelforum.com/member.php...o&userid=36547
View this thread: http://www.excelforum.com/showthread...hreadid=563019

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

Sumif with multiple criteria
 

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

Thanks!


--
jn77
------------------------------------------------------------------------
jn77's Profile: http://www.excelforum.com/member.php...o&userid=36547
View this thread: http://www.excelforum.com/showthread...hreadid=563019

Sumif with multiple criteria
 

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 all


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

--
MDubbelboer
------------------------------------------------------------------------
MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
View this thread: http://www.excelforum.com/showthread...hreadid=563019