count rows with content in some columns
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)
|