count rows with content in some columns
 

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

count rows with content in some columns
 

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)

count rows with content in some columns
 

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)

count rows with content in some columns
 

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)

count rows with content in some columns
 

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

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)

count rows with content in some columns
 

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" schreef in bericht
...
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

count rows with content in some columns
 

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

count rows with content in some columns
 

Biff is an old nickname. T = Tony.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

count rows with content in some columns
 

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

count rows with content in some columns
 

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. :-)
--
David Biddulph

"T. Valko" wrote in message
...
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

count rows with content in some columns
 

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)