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

=COUNTIF(D12:I21,"")

This works for me.

Cheers,
Jason Lepack

Dos Equis wrote:
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

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

--
Don Guillett
SalesAid Software

"Dos Equis" wrote in message
ups.com...
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

=COUNTIF(D12:I21,"<"&"")


"Dos Equis" wrote:

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

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.


Dos Equis wrote:
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

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

Regards,

Peo Sjoblom



Dos Equis wrote:
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

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


with @tiscali.co.uk


"Dos Equis" wrote in message
ups.com...
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

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 wrote:
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


with @tiscali.co.uk


"Dos Equis" wrote in message
ups.com...
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

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


with @tiscali.co.uk


"Dos Equis" wrote in message
ps.com...
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