| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Columns, Return Content | Excel Worksheet Functions | |||
| Count rows with criteria from two columns? | Excel Worksheet Functions | |||
| Count if in multiple rows / columns | Excel Discussion (Misc queries) | |||
| Count entries in columns and rows | Excel Worksheet Functions | |||
| Reduce columns and rows count? | Excel Worksheet Functions | |||
Hybrid Mode
