Do you need the double unary minus before the MMULT?

MMULT(--(A1:C3<""),{1;1;1})

That will return an array of numbers that are the count of non-empty cells
*per row*.

...........A..........B..........C
1........x........................x
2...................................
3....................x.............

{2;0;1}

Since we're counting the number of rows that are not empty we need to test
that array of counts for the condition 0.

I'm sure you know this so this is for the benefit of others.

MMULT(--(A1:C3<""),{1;1;1})0

Will return an array of logical TRUE or FALSE:

20 = TRUE
00 = FALSE
10 = TRUE

The double unary coerces these logicals to numbers:

--TRUE = 1
--FALSE = 0

--({TRUE;FALSE;TRUE}) = {1;0;1}

SUMPRODUCT({1;0;1}) = 2

So:

=SUMPRODUCT(--(MMULT(--(A1:C3<""),{1;1;1})0))

=2 (2 *rows* aren't completely empty)


--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Do you need the double unary minus before the MMULT?
Doesn't MMULT return a number? Doesn't a double unary minus leave a
number unchanged?
--
David Biddulph

"T. Valko" wrote in message
...
I guess that I could use something like
--(MMULT(--(AK1:AO1000<""),{1;1;1;1;1})0)

Just need to wrap it inside SUMPRODUCT:

=SUMPRODUCT(--(MMULT(--(AK1:AO1000<""),{1;1;1;1;1})0))

--
Biff
Microsoft Excel MVP


"Jack Sons" wrote in message
...
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