Sumproduct not returning expected results

[Dos Equis]

Hi all,

I am using this formula to evaluate a block of cells:

=SUMPRODUCT(--($D$12:$D$21<>" "),--($E$12:$E$21<>" "),--($F$12:$F$21<>"
"),--($G$12:$G$21<>" "),--($H$12:$H$21<>" "),--($I$12:$I$21<>" "))

The return is 10 but should be 22. If it's set up correctly this
should be counting the number of cells which are not blank. As far as
I can tell, the only portion which is returning a count is the
$D$12:$D$21 part, all others are lost to me. Thanks for any help.

Byron

 

[Jason Lepack]

=COUNTIF(D12:I21,"")

This works for me.

Cheers,
Jason Lepack

 

[Don Guillett]

Will this not do?
=COUNTIF(D12:I21,"<>" &" ")

 

[Jason Lepack]

I appologize, you wanted the non blank cells.

=COUNTA(D12:I21)

will do the job, but if you really want to use sumproduct then:

=SUMPRODUCT(--(D12:I21<>""))

Is the one for you.

 

[Peo Sjoblom]

I suspect you got your answers, note that a blank is "" in Excel, not
" " which is a space.

Regards,

Peo Sjoblom

 

[Sandy Mann]

Byron,

Others have given you functions to return the answer that you want.

The reason that your SUMPRODUCT() formula does not work is firstly as Peo
pointed out, <>" " is not checking for a blank cell but rather checking that
the cell does not have a space in it.

Even then however your formula would not work because it would then be
checking not for filled cells but rather filled ROWS

For example if cell D12 has some data in it then --($D$12:$D$21<>" ") will
indeed return an array {1;0;0;0;0;0;0;0;0;0}
However, if E12 does not have any data then --($E$12:$E$21<>" ") will return
an array of {0;0;0;0;0;0;0;0;0;0}

when the first two elements are multiplied together we have 1 * 0 which of
course is 0. So if there is *ANY* cell in a row without any data in it,
then
the chain of multiplications of that element of the 6 arrays will evaluate
to zero. Only if all cells in that row have data will the multiplication of
the elements of the arrays result in 1 ie 1*1*1*1*1*1 =1

Thus all cells in a row need to have data to return anything other than
zero.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Dos Equis]

Thank you all for the information and soloutions; it now works. Sandy,
Thank you for the education. I will hopefully retain that knowledge
and not make the same mistake in teh future.

Byron

 

[Sandy Mann]

and not make the same mistake in teh future.

That's all any of us can hope for <g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk