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.

 

[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.

 

[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.