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)