I created a form where by using a
COUNT:=SUMPRODUCT((TEXT(Order!A2:A65535,"m/dd/yy")=TEXT('Order Confirmation'!$J$34,"m/dd/yy"))*(((Order!J2:J65535>=1)+(Order!I2:I65535>=1))>0)*(Order!A2:A65535<>"")*(Order!J2:J65535<>"")*(Order!I2:I65535<>""))
AND:=IF(ROWS(B$38:B38)>$M$34,"",INDEX(Order!B$2:B$65535,AGGREGATE(15,6,(ROW(Order!$A$2:$A$65535)-ROW(Order!$A$2)+1)/((Order!$A$2:$A$65535=$J$34)*((Order!$J$2:$J$66527>=$K$34)+(Order!$I$2:$I$65535>=$L$34))>0),ROWS(B$38:B38))))
I can extract specific records based on multiple criteria. Currently if I have 2 records that meet all of the criteria but with different quantity information I will see both records. I would like to modify the formulas above so that when this occurs it combines the 2 records and sums the quantity so that I only see one record. Is this possible?
Example: The above formulas return
Date Item QTY
3/18/14 AMSBLU 1
3/18/14 BAPAUS 0
3/18/14 CHEHOTL 2
3/18/14 CHEHOTL 1
I want to see:
Date Item QTY
3/18/14 AMSBLU 1
3/18/14 BAPAUS 0
3/18/14 CHEHOTL 3
COUNT:=SUMPRODUCT((TEXT(Order!A2:A65535,"m/dd/yy")=TEXT('Order Confirmation'!$J$34,"m/dd/yy"))*(((Order!J2:J65535>=1)+(Order!I2:I65535>=1))>0)*(Order!A2:A65535<>"")*(Order!J2:J65535<>"")*(Order!I2:I65535<>""))
AND:=IF(ROWS(B$38:B38)>$M$34,"",INDEX(Order!B$2:B$65535,AGGREGATE(15,6,(ROW(Order!$A$2:$A$65535)-ROW(Order!$A$2)+1)/((Order!$A$2:$A$65535=$J$34)*((Order!$J$2:$J$66527>=$K$34)+(Order!$I$2:$I$65535>=$L$34))>0),ROWS(B$38:B38))))
I can extract specific records based on multiple criteria. Currently if I have 2 records that meet all of the criteria but with different quantity information I will see both records. I would like to modify the formulas above so that when this occurs it combines the 2 records and sums the quantity so that I only see one record. Is this possible?
Example: The above formulas return
Date Item QTY
3/18/14 AMSBLU 1
3/18/14 BAPAUS 0
3/18/14 CHEHOTL 2
3/18/14 CHEHOTL 1
I want to see:
Date Item QTY
3/18/14 AMSBLU 1
3/18/14 BAPAUS 0
3/18/14 CHEHOTL 3