Sumif with multiple criteria

[jn77]

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!

[MDubbelboer]

=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

[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

"jn77" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Bondi]

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

[jn77]

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!

[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 all