[Solved] Counting different entries with a "plus"
[Solved] Counting different entries with a "plus"
Greetings.
I need to count the number of "different" entries in several cells regarding several restrictions:
- The cells are in rows but the columns aren't side by side
- Al the rows are below each other
- In a row can only be "1"s or letters
- When a row only has "1"s they should be counted all as different
- When a row has letters it hasn't "1"s
- Some letters can be repeated, but should only be counted the different entries
To better understand my problem, the following link has an example of my problem.
The leftest column has the expected result.
http://www.mediafire.com/?5fit9dcs0u39pjz
Thank you for your attention
JLeite
I need to count the number of "different" entries in several cells regarding several restrictions:
- The cells are in rows but the columns aren't side by side
- Al the rows are below each other
- In a row can only be "1"s or letters
- When a row only has "1"s they should be counted all as different
- When a row has letters it hasn't "1"s
- Some letters can be repeated, but should only be counted the different entries
To better understand my problem, the following link has an example of my problem.
The leftest column has the expected result.
http://www.mediafire.com/?5fit9dcs0u39pjz
Thank you for your attention
JLeite
Last edited by Hagar Delest on Sat Jan 08, 2011 6:55 pm, edited 1 time in total.
Reason: Tagged solved
Reason: Tagged solved
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
-
- Volunteer
- Posts: 918
- Joined: Sun May 31, 2009 1:35 am
- Location: Sydney, Australia
Re: Counting different entries with a "plus"
Assuming your expected result in A11, 5, should be 6 (|A|B|C|D|E|F| is 6 different letters), try...in A1 filled down to A13.
If your 5 in A11 is correct then I am missing something.
Ken Johnson
Code: Select all
=IF(SUMPRODUCT(ISNUMBER(C2:M2))>0;SUM(C2:M2);SUMPRODUCT((C2:M2<>"")/(COUNTIF(C2:M2;C2:M2)+(C2:M2=""))))
If your 5 in A11 is correct then I am missing something.
Ken Johnson
AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Re: Counting different entries with a "plus"
Greetings.ken johnson wrote:Assuming your expected result in A11, 5, should be 6 (|A|B|C|D|E|F| is 6 different letters), try...in A1 filled down to A13.Code: Select all
=IF(SUMPRODUCT(ISNUMBER(C2:M2))>0;SUM(C2:M2);SUMPRODUCT((C2:M2<>"")/(COUNTIF(C2:M2;C2:M2)+(C2:M2=""))))
If your 5 in A11 is correct then I am missing something.
Ken Johnson
You're righ,t I should have put 6 instead of 5. I counted wrongly, that's why I need this to be done by the computer.
I'll try what you propose and I'll come back to you.
Best regards
JLeite
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
Re: Counting different entries with a "plus"
Almost perfect, because in the empty columns sometimes there are numbers or letters. I'll create extra columns equals to the ones I want to count, side by side, unless you have a more elegant solution.
Best regards
JLeite
Best regards
JLeite
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
Re: Counting different entries with a "plus"
If this has answered your question please go to your first post and use the Edit button, and add [Solved] to the start of the title. You should select the green tick icon at the same time.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
Gurkha Welfare Trust
-
- Volunteer
- Posts: 918
- Joined: Sun May 31, 2009 1:35 am
- Location: Sydney, Australia
Re: Counting different entries with a "plus"
This is an array formula and must therefore be entered using the Ctrl+Shift+Enter (PC keyboard) key combination...
Also, when you use the fill handle to get it into the other rows you must make sure the Ctrl key is held down while dragging the fill handle.
Ken Johnson
Code: Select all
IF(SUM(ISNUMBER(C2);ISNUMBER(E2);ISNUMBER(G2);ISNUMBER(I2);ISNUMBER(K2);ISNUMBER(M2))>0;C2+E2+G2+I2+K2+M2;SUMPRODUCT(1/COUNTIF(IF(ISODD(COLUMN($C2:$M2));IF($C2:$M2="";".";$C2:$M2);".");IF(ISODD(COLUMN($C2:$M2));IF($C2:$M2="";".";$C2:$M2);".")))-1)
AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Re: [Solved] Counting different entries with a "plus"
Thank you very much, ken johnson
I'll stick to your first proposal, because my original spreadsheet has too may columns (and growing) to enter a formula that uses explicitly, each one.
The result from your effort is amazing, just what I needed. Thank you very much, once again.
I only regret that my post was considered "solved" by someone before me, I would gladly consider it solved if I had a chance after i try the formula in the real speadsheet.
Best regards and thank you once more.
JLeite
I'll stick to your first proposal, because my original spreadsheet has too may columns (and growing) to enter a formula that uses explicitly, each one.
The result from your effort is amazing, just what I needed. Thank you very much, once again.
I only regret that my post was considered "solved" by someone before me, I would gladly consider it solved if I had a chance after i try the formula in the real speadsheet.
Best regards and thank you once more.
JLeite
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
-
- Volunteer
- Posts: 918
- Joined: Sun May 31, 2009 1:35 am
- Location: Sydney, Australia
Re: [Solved] Counting different entries with a "plus"
This array formula avoids the problem of explicit column references...
Ken Johnson
Code: Select all
IF(SUM(IF(ISODD(COLUMN($C2:$M2));ISNUMBER($C2:$M2);0))>0;SUM(IF(ISODD(COLUMN($C2:$M2));$C2:$M2;0));SUMPRODUCT(1/COUNTIF(IF(ISODD(COLUMN($C2:$M2));IF($C2:$M2="";".";$C2:$M2);".");IF(ISODD(COLUMN($C2:$M2));IF($C2:$M2="";".";$C2:$M2);".")))-1)
AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Re: Counting different entries with a "plus"
Greetings.JLeite wrote:Greetings.ken johnson wrote:Assuming your expected result in A11, 5, should be 6 (|A|B|C|D|E|F| is 6 different letters), try...in A1 filled down to A13.Code: Select all
=IF(SUMPRODUCT(ISNUMBER(C2:M2))>0;SUM(C2:M2);SUMPRODUCT((C2:M2<>"")/(COUNTIF(C2:M2;C2:M2)+(C2:M2=""))))
If your 5 in A11 is correct then I am missing something.
Ken Johnson
You're righ,t I should have put 6 instead of 5. I counted wrongly, that's why I need this to be done by the computer.
I'll try what you propose and I'll come back to you.
Best regards
JLeite
Can you please explain me the first formula you've proposed, for me to understand it and make good use of its concepts in future situations.
IF(SUMPRODUCT(ISNUMBER(C2:M2))>0;SUM(C2:M2);SUMPRODUCT((C2:M2<>"")/(COUNTIF(C2:M2;C2:M2)+(C2:M2=""))))
Best regards.
JLeite
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
-
- Volunteer
- Posts: 918
- Joined: Sun May 31, 2009 1:35 am
- Location: Sydney, Australia
Re: [Solved] Counting different entries with a "plus"
See if my explanation in the attached Writer doc makes any sense.
Ken JohnsonAOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Re: [Solved] Counting different entries with a "plus"
Greetings, Ken.ken johnson wrote:See if my explanation in the attached Writer doc makes any sense.Ken Johnson
Now, I've seen your explanation and it seems a bit dense, for me I mean. I'll visit it several times for the next days to come and I'm sure I'll end by understand it, so thank you again.
Best regard
JLeite
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1