Sumproduct & Empty Cell

M

mhoffmeier

I have a lengthy sumproduct that relies in part on the following test:

=SUMPRODUCT(--('Standard cost codes'!$E$2:$E$473=E124))

'Standard cost codes'!$E$2:$E$473 is a list of cost code descriptions
in text.
E124 is where the descriptions are filled out on another sheet.

When E124 contains a value in the range 'Standard cost codes'!$E$2:$E
$473, it works just fine, evaluating the appropriate cell in 'Standard
cost codes'!$E$2:$E$473 as 1, the others as 0.

If E124 is blank, however, it evaluates all the cells in 'Standard
cost codes'!$E$2:$E$473 as 1. Rather than returning FALSE, and a
multiplier of zero, since there are no blank cells, it provides TRUE,
and a mulitplier of one for all cells, returning 472 when standing
alone as shown above.

I've worked around this with an if(isblank(E124),"",....., but I don't
understand why excel thinks a blank cell equals cells that have text
in them.

Any ideas what I've got wrong here?
 
M

Max

Try either:

=IF(E124="",0,SUMPRODUCT(--('Standard cost codes'!$E$2:$E$473=E124)))

or

=SUMPRODUCT(('Standard cost codes'!$E$2:$E$473=E124)*('Standard cost
codes'!$E$2:$E$473<>""))

The former would be the simpler / better
 
M

mhoffmeier

Try either:

=IF(E124="",0,SUMPRODUCT(--('Standard cost codes'!$E$2:$E$473=E124)))

or

=SUMPRODUCT(('Standard cost codes'!$E$2:$E$473=E124)*('Standard cost
codes'!$E$2:$E$473<>""))

The former would be the simpler / better
Click to expand...

I realize both of those fix the problem. But, why is it that you must
tell SUMPRODUCT not to return TRUE for the condition "" when the cells
in the range are not blank?
 
M

Max

I realize both of those fix the problem. But, why is it that you must
tell SUMPRODUCT not to return TRUE for the condition "" when the cells
in the range are not blank?
Click to expand...

Blank cells are evaluated as zeros within formulas by Excel.
That's the way it's implemented.
 
M

mhoffmeier

Blank cells are evaluated as zeros within formulas by Excel.
That's the way it's implemented.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
Click to expand...

Thanks for the reply.
There are no blank cells in the range.
Sumproduct is returning TRUE that all the cells in the range = BLANK
I don't understand that.
 
M

Max

Null strings: "" returned by formulas within the source range are treated as
being equal to blank cells in Excel's evaluation.
 
Top