How do i get Sumproduct to ignore empty cells?

[Sanchez007]

Hell everybody,

I have a small sumproduct formular that checks for "Criteria" values in a matrix, if it finds the value then it adds the corrosponding $ value from another colum.

this works sweet except for when the corrosponding value is 0.
it treats cells with nothing in them as 0 as well.

Is there a simple fix to sort this out?

(now for the long version)

Example:
For a building I want to calculate how much each floor cost to build.

Formular is:
=SUMPRODUCT((I2:M26=A10)*D2:D26)

Now the Matrix I2:M26 contains the Floors into which the item was installed.
The Colum D2:D26 contains the cost per floor for that particular item eg a cable that was installed over 3 floors (5,6 and 7) cost $3000 so $1000 would be allocated to the total of each of those 3 floors ($1000 to floor 5, 1000 to floor 6 and 1000 to floor 7)
A10 is the floor number.

this all works sweeet, except for floor 0.
When we get to floor 0 it thinks that all the cells in the matrix with nothing in them are to be treated as floor 0

Any ideas would be greatly appreciated

Sanchio

 

[Weaver]

try

=SUMPRODUCT((I2:M26=A10&"")*D2:D26)

(not tested)

 

[Sanchez007]

It just returned a 0, it should return a 15?

 

[Weaver]

Do you have some sample data?

 

[xenou]

Not sure.
Maybe:
=SUMPRODUCT(--(I2:M26=A10),--(Len(I2:M26)>0),D2:D26)

 

[Sanchez007]

This is not the original data set im working with, it is a simplefied version.
Basically what I want it to do is every time there is a number from Colum A in Matrix I2:M26 then it needs to add the corrosponding number from Colum D to the correct cell in Colum C.
I hope that was not too complicated to follow

the sumproduct works sweet except for when there is a 0 in Colum A, then it adds all cells with 0 or blank in matrix I2:M26.

The cell C2 should read 15 and not 190 because the 0 occurs 3 times in the Matrix and the corrosponding colum D numbers are all 5

 

[barry houdini]

Try this formula in C2 copied down:

=SUMMENPRODUKT((I$2:M$26=A2)*(I$2:M$26<>"")*D$2:D$26)

 

[Sanchez007]

Try this formula in C2 copied down:

=SUMMENPRODUKT((I$2:M$26=A2)*(I$2:M$26<>"")*D$2:D$26)

That is fantastic, it worked, but now I need to know why/how lol...
Why did it work? or how does it work?

...(I$2:M$26<>"")... what does this part do?


sanchio

 

[barry houdini]

That part checks that the cells are not equal (<>) to blank ("")......it's one of several approaches you could use. Xenou's LEN check would also work (checking that the cell length > 0) but you need the syntax I suggested, i.e.

=SUMMENPRODUKT((I$2:M$26=A2)*(LEN(I$2:M$26)>0)*D$2:D$26)

 

[Sanchez007]

Ah, very clever, yet simple.

Thank you to all those that had thier input.
Case closed.

Sanchio