# Excel formula to sumproduct but with multiple conditions

1. ## Excel formula to sumproduct but with multiple conditions

Hello!

Can anyone advise on the following I have a long list of products (rows) and data columns for weight, price and units sold. I want to get to a single figure of unit which satisfy two criteria, falls within two given weight points and within two given price points.

I have worked a formula to give a sum of products within two data points e.g. weight point 0-200g, using
= SUMPRODUCT((WEIGHTLIST>=LOW WEIGHT CONDITION)*(WEIGHTLIST <=HIGH WEIGHT CONDITION))*(UNITS SOLD)
=(SUMPRODUCT((\$H\$17:\$H\$5000>=A5)*(\$H\$17:\$H\$5000<=B5)*\$D\$17:\$D\$5000))

I would like to complicate (!) this by adding in an additional two data points (for a different column), which must also be satisfied e.g. price point between £0-£3 and falls into the weight bracket 0-200g, any suggestions would be hugely appreciated!

Thanks, Danielle  Register To Reply

2. ## Re: Excel formula to sumproduct but with multiple conditions

Let's say this other column is J and you put low value (0) in C5 and high value (3) in D5. Just add those condtions to your existing formula.

=(SUMPRODUCT((\$H\$17:\$H\$5000>=A5)*(\$H\$17:\$H\$5000<=B5)*(\$J\$17:\$J\$5000>\$C\$5)*(\$J\$17:\$J\$5000 <= \$D\$5)*(\$D\$17:\$D\$5000))
Does that work?  Register To Reply

3. ## Re: Excel formula to sumproduct but with multiple conditions

Absolutely fab! Thats worked a treat!

Thanks!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 