Set up the array or change the formula?

[vini.v4]

Hi community,

I'm using the formula on E10 to calculate the quantity of slats I use depend on the size of the product


When I type any combination of these following numbers (150,200,250,300,350,400,450,500,550,600,650,700,800,900,1000,1100,1200,1350,1500,1650,1800,2000,2200,2400) on E3toE10 and F3toF10, the formula on E10 use the table bellow to search for the equivalent value(lookup) and sum all these values.

I tryied select on the lookup range the whole table but as I'm using arrays was returning wrong values(the table of heigh 100 is returning correct), So I decided to use a lookup for every single size different of 100, the problem is that I have around 300 different combinations and it would be 300 lookups on the same formula (when I type another lookup on the current formula the excel is not highlighting anymore), also the maximum SUM is 255.


Do you guys know how I can SUM these values easier using another formula or changing the array setup to use the whole table?

thank you

[XLent]

I think when you mention your formula you're referring to I10 as opposed to E10 -- the below would give the same result:

Please Login or Register  to view this content.

your current use of LOOKUP implies you could have inexact criteria yet the "options" for E3:F10 (as listed in prior post) all exist - as such I've assumed exact match

if the above isn't what you need post back with some expected results for variety of different E3:F10 combinations.

[vini.v4]

Hey XLent, that completely helped me

I added this part in bold to multiply with the quantity and it`s working perfectly.

=SUMPRODUCT(SUMIFS($E$23:$E$320,$A$23:$A$320,$E$3:$E$10,$B$23:$B$320,$F$3:$F$10)*($D$3:$D$10))