Pull values from a column based off dual criteria from another column

[mt03530]

Hello!

I am trying to accomplish a task in Excel to avoid multiple "messy" manual steps.

This is a simplified version of my workbook:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Item No.[/TD]
[TD]Description[/TD]
[TD]Pack[/TD]
[TD]Size[/TD]
[TD]Color[/TD]
[TD]Quantity[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]1111[/TD]
[TD]Item 1[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]Red[/TD]
[TD]450[/TD]
[TD]2016[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]1111[/TD]
[TD]Item 1[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]Red[/TD]
[TD]300[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]1112[/TD]
[TD]Item 2[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]Tan[/TD]
[TD]300[/TD]
[TD]2016[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]1112[/TD]
[TD]Item 2[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]Tan[/TD]
[TD]300[/TD]
[TD]2016[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]1113[/TD]
[TD]Item 3[/TD]
[TD]4[/TD]
[TD]32 [/TD]
[TD]Blue[/TD]
[TD]400[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]1113[/TD]
[TD]Item 3[/TD]
[TD]4[/TD]
[TD]32[/TD]
[TD]Blue[/TD]
[TD]450[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]1114[/TD]
[TD]Item 4[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]Tan[/TD]
[TD]300[/TD]
[TD]2017[/TD]
[/TR]
</tbody>[/TABLE]



I need to figure out of the items my employees sold, which were new in 2017 and the total quantities. Using the above table, the result I want is:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Item No.[/TD]
[TD]Description[/TD]
[TD]Pack[/TD]
[TD]Size[/TD]
[TD]Color[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]1113[/TD]
[TD]Item 3[/TD]
[TD]4[/TD]
[TD]32[/TD]
[TD]Blue[/TD]
[TD]850[/TD]
[/TR]
[TR]
[TD]1114[/TD]
[TD]Item 4[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]Tan[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]



Items 1111 and 1112 were both sold in 2016, so I do not want them. Amy and Bill both sold Item 1113 only in 2017, so I want the add their quantities for that item. And Item 1114 was sold only in 2017 by one person, so I basically just want to copy that line.

I can't figure out how to pick apart which items were only sold in 2017 and sum the quantities by item number. I'm not sure if there is a formula out there or if I need a macro.

Any help is appreciated. Thanks!

 

[Johnny C]

A SUMIFS should do it...

=SUMIFS($G$2:$G$100,$H2:$H$100,2017,$C$2:$C$100,$J3,$D$2:$D$100,$K2,$E$2:$E$100,$L2,$F$2:$F$100,$M2)

for each combination of criteria in columns J-M

In col J you'd have 'Item 1' etc, Col K you'd have Pack, L size and M colour. But you'd need every combination which might me rather a lot.

In that case you want it dynamic. It gets trickier, you'd need a 'counter' column that increments by 1 every time a row with 2017 is in, then some OFFSET/MATCH or INDEX/MATCH to look up each new instance of 1,2,3 etc in the counter column and OFFSET/MATCH to pull the labels for each new instance of the counter column

So in column J (starting at row 2) you'd have =IF($H2=2017,$H1+1,$H1)

to look up where the 2nd instance of 2017 was in say col L put 1,2,3 etc. Then in col M put =MATCH(L2.$L$2:$L$100,0)

Then for Item, Pack, size, quantity in cols N across =OFFSET(B$1,$M2,0) and copy across then use the SUMIFS above on those columns to pull the amount for each used combination

This might include duplicates if there are any in the source data, in which case you need to create a key with say item/pack/size/colour and check if that key has already appeared before incrementing your counter.

 

[mt03530]

A SUMIFS should do it...

=SUMIFS($G$2:$G$100,$H2:$H$100,2017,$C$2:$C$100,$J3,$D$2:$D$100,$K2,$E$2:$E$100,$L2,$F$2:$F$100,$M2)

for each combination of criteria in columns J-M

In col J you'd have 'Item 1' etc, Col K you'd have Pack, L size and M colour. But you'd need every combination which might me rather a lot.

In that case you want it dynamic. It gets trickier, you'd need a 'counter' column that increments by 1 every time a row with 2017 is in, then some OFFSET/MATCH or INDEX/MATCH to look up each new instance of 1,2,3 etc in the counter column and OFFSET/MATCH to pull the labels for each new instance of the counter column

So in column J (starting at row 2) you'd have =IF($H2=2017,$H1+1,$H1)

to look up where the 2nd instance of 2017 was in say col L put 1,2,3 etc. Then in col M put =MATCH(L2.$L$2:$L$100,0)

Then for Item, Pack, size, quantity in cols N across =OFFSET(B$1,$M2,0) and copy across then use the SUMIFS above on those columns to pull the amount for each used combination

This might include duplicates if there are any in the source data, in which case you need to create a key with say item/pack/size/colour and check if that key has already appeared before incrementing your counter.

I'm pretty sure I followed that, but I don't see where this accounts for the removal of items sold in 2016 as well. If I understand correctly, your formula would pull item 1111 as well, which I do not want because that item was also sold in 2016.

 

[Johnny C]

In that case put an IF test in ... IF(SUMIFS(<as above but with 2017 replace by "<>2017"),0,SUMIFS(<as above>). If you're doing the dynamic version you'd need to incorporate that before you increment the counter column