Sum from 2 lists based on 2 constant 2 changing variables

[MHudson]

Hi all...first time poster, long-time reader.

Basically, I want to look at pieces in our inventory and pieces on order from our supplier. If I have pieces in inventory that are greater or equal to in both width and length than the part that has been ordered, I can cancel the order and cut it out of the larger piece that I already have on hand. So I need to sum the pieces I have in inventory that are greater in width & length - BUT, are also the same Brand and Grade to the pieces that are on order. Eliminating costs is what it's all about these days isn't it???


My spreadsheet goes like this: 6 columns:

Brand Grade Width Length CountOnOrder CountInventory

A 160 86 150 0 2

A 160 87 150 13 0

A 160 88 151 0 5

B 160 90 160 3 0

B 160 91 161 0 6


So from the above, I would need to see that for Brand A & Grade 160 there are 5 pcs I can cancel that are on order. And Brand B & Grade 160 there are 3 pieces that I can cancel that are on order. Any ideas?

 

[Hui]

MHudson


Firstly welcome to the Chandoo.org Forums.


Not sure but I think you need a Sumproduct here

To find total Count on Order where Brand=A & Grade=160 & Width>X & Length>Y use:

=SUMPRODUCT((A:A="A")*(B:B=160)*(C:C>80)*(D:D>140),(E:E))


To find total Count Inventory where Brand=A & Grade=160 & Width>X & Length>Y use:

=SUMPRODUCT((A:A="A")*(B:B=160)*(C:C>80)*(D:D>140),(F:F))


Adjust size and columns to suit

 

[MHudson]

Thanks Hui, almost there.

The easiest way I can explain it is: Sum (or even highlight) the CountInventory with greater width and length that are the same Brand and Grade. So for the A-160's the formula would only return 5 because the 2 pcs on line 2 are smaller than the 13 on order on line 3.

My head's about to explode, I tried tweaking your 2nd formula that returns 7, but no luck. It seems I would need it to stop at every CountInventory value greater than 0 and determine if there are any CountOnOrders that are smaller than the length & width for every brand & grade. Confusing enough???


Thanks