Glad you got it sorted out, but curious why this did not work.

Actually I did mean ROW(1:85) (which in this context generates indices
for the 85 columns in AK:DQ, for the benefit of MMULT). This lets you
set up an arbitrarily large range to test without having to type out
{1;1;1;1....}.


Jack Sons wrote:
You mean
=SUM(--(MMULT(--(AK1:DQ1000<""),ROW(1:1000))0)) ?
Sorry, neither will work.

Jack.

"smartin" schreef in bericht
...
Maybe this array formula for the full range (85 columns):

=SUM(--(MMULT(--(AK1:DQ1000<""),ROW(1:85))0))

Jack Sons wrote:
Franz,

Thanks so far, but I indeed want to count a row as 1 if one or more
cells in the columns AK to AO are not empty.

By the way, the formula you showed is alright for a not to large number
of columns, but if I want to look at the columns AK to DQ (and probably
not al columns in that range) it wil become very laborious. That's why I
thought of the MMULT formula.

Other suggestion?

Jack.

"Franz Verga" schreef in bericht
...
Jack Sons wrote:
Hi all,

I want to count the number of rows (1 to 1000) that have content in
columns AK to AO. I want to do it with a formula (so no helper column
or VBA). I guess that I could use something
like --(MMULT(--(AK1:AO1000<""),{1;1;1;1;1})0)
but I can't get it working.
Your help will be appreciated.

Jack Sons
The Netherlands
Hi Jack,

the following formula will count all the row with at least 1 value:

=SUMPRODUCT((AK1:AK1000<"")+(AL1:AL1000<"")+(AM1 :AM1000<"")+(AN1:AN1000<"")+(AO1:AO1000<""))

What about the row with more than one value (for example, if you have a
value in AK10 and another in AL10)? How do you want to count such rows?
As 1 or as 2, if you want to count as only 1, I have to think a little
bit more how to modify the formula...


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy (now in Berlin)