Sumproduct used when certain criteria is met

[jgcramer]

I am working on this database and need it to add up the product of two columns that meet certain criteria, i have figured out how to sum up the number that meet the criteria but not how to multiple the two columns after they find which ones meet the criteria. so in the attachment Sumproduct example.xlsm you can see the database where it has the customer information listed, what i need is to have the sum of the product of quantity and capacity for any customer that has a small model size, has a ship date for June 14, and possibility of over 70% I'm trying to use

Please Login or Register  to view this content.

without success. any suggestions would be appreciated.

[XOR LX]

Hi,

Try =SUMPRODUCT((I2:I12="Small")*(H2:H12=DATE(2014,6,13))*(F2:F12>0.7)*(D2:D12*E2:E12))

Regards

[Jonmo1]

You need to coerce the date string "6/13/2014" to a real date..

Try
=SUMPRODUCT(--(H2:H12="6/13/2014"+0),--(I2:I12="Small"),--(F2:F12>70%),D2:D12,E2:E12)

[TMS]

=SUMPRODUCT(--(H2:H12=DATEVALUE("13/06/2014")),--(I2:I12="Small"), D2:D12,E2:E12)


Regards, TMS

[Norie]

I think the problem is the date, in column H you have 'real' dates and in the formula the date is text.

Try wrapping the date in the formula with DATEVALUE.

You are also missing the possibilty criteria.

Please Login or Register  to view this content.

[jgcramer]

Wow, thanks everyone, it works out great.