count rows with content in some columns

[Jack Sons]

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

 

[Franz Verga]

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)

 

[Jack Sons]

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.

 

[smartin]

Maybe this array formula for the full range (85 columns):

=SUM(--(MMULT(--(AK1Q1000<>""),ROW(1:85))>0))

 

[T. Valko]

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))

 

[Jack Sons]

You mean
=SUM(--(MMULT(--(AK1Q1000<>""),ROW(1:1000))>0)) ?
Sorry, neither will work.

Jack.

 

[Jack Sons]

T,

As always, your solution did it. I was close myself, but I used countif
instead of count. Thank you again.

By the way, I wonder, the "Biff" at the end of your messages, is that your
name (if so, what stands T for?) or does it mean a UNIX mail notification
program or perhaps a Usenet/internet pseudonym ? As a foreigner, not very
good in English, I can't figure it out. Please unveil the secret.

Jack.

 

[David Biddulph]

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?

 

[T. Valko]

Biff is an old nickname. T = Tony.

Thanks for the feedback!

 

[T. Valko]

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:

2>0 = TRUE
0>0 = FALSE
1>0 = 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)

 

[David Biddulph]

Sorry. Yes, I'd miscounted the parentheses. I realise now, of course, that
the double unary wasn't applying to MMULT(...), but to (MMULT(...)>0)
I'll go back to sleep.

 

[smartin]

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 AKQ, 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....}.