| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return multiple rows of data based on criteria | Excel Worksheet Functions | |||
| SUMIF multiple criteria | Excel Discussion (Misc queries) | |||
| Counting by multiple criteria | Excel Worksheet Functions | |||
| SUMIF with multiple criteria | Excel Worksheet Functions | |||
| SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
