Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#5
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#7
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 |
#8
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 |
#9
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 |
#10
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 |
#11
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
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 |