PDA

View Full Version : SUMPRODUCT help needed!

Martin B.
01-17-2011, 06:32 AM
[Great genius people of the VBA express forum. :bow:

I am currently working on a SUMPRODUCT formula. For one of the criteria’s I want it to grab the value from another cell. And in this sell I want to add up several criteria’s. For example cell value: "1, 2, 3, 4". This would mean that it would look for values 1, 2, 3 and 4.

I also want to have multiple values in the cell that refers to values that start with a certain value. For example cell value: 093%, 094%, 095%. This would mean that it would look for values that start with 093, 094 and 095.

Does anyone have a resolution for this?

Many thanks,
Martin

mikerickson
01-17-2011, 10:44 AM
It would help us understand better if you attached a workbook showing example inputs and the desired result.

As a general rule, putting mulitple pieces of data in one cell adds unnesseary complication. The comma delimited entries that you mention would better be entered as three cells.

Martin B.
01-18-2011, 08:20 AM
Okay. I abanded that idea for just having several cells. But after integrating one I cant get it to work:

=SUMPRODUCT(--(Sheet2!\$A\$2:\$A\$65536=\$J\$11),--(Sheet2!\$D\$2:\$D\$65536=\$J\$15),--(LEFT(Sheet2!\$B\$2:\$B\$65536,2)=\$J\$24),Sheet2!\$E\$2:\$E\$65536)