[Solved] Counting different entries with a "plus"

Discuss the spreadsheet application
Post Reply
User avatar
JLeite
Posts: 31
Joined: Mon Jan 03, 2011 2:12 am
Location: Porto, Portugal

[Solved] Counting different entries with a "plus"

Post by JLeite »

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
Last edited by Hagar Delest on Sat Jan 08, 2011 6:55 pm, edited 1 time in total.
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
ken johnson
Volunteer
Posts: 918
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: Counting different entries with a "plus"

Post by ken johnson »

Assuming your expected result in A11, 5, should be 6 (|A|B|C|D|E|F| is 6 different letters), try...

Code: Select all

=IF(SUMPRODUCT(ISNUMBER(C2:M2))>0;SUM(C2:M2);SUMPRODUCT((C2:M2<>"")/(COUNTIF(C2:M2;C2:M2)+(C2:M2=""))))
in A1 filled down to A13.
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.
User avatar
JLeite
Posts: 31
Joined: Mon Jan 03, 2011 2:12 am
Location: Porto, Portugal

Re: Counting different entries with a "plus"

Post by JLeite »

ken johnson wrote:Assuming your expected result in A11, 5, should be 6 (|A|B|C|D|E|F| is 6 different letters), try...

Code: Select all

=IF(SUMPRODUCT(ISNUMBER(C2:M2))>0;SUM(C2:M2);SUMPRODUCT((C2:M2<>"")/(COUNTIF(C2:M2;C2:M2)+(C2:M2=""))))
in A1 filled down to A13.
If your 5 in A11 is correct then I am missing something.

Ken Johnson
Greetings.
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
User avatar
JLeite
Posts: 31
Joined: Mon Jan 03, 2011 2:12 am
Location: Porto, Portugal

Re: Counting different entries with a "plus"

Post by JLeite »

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
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
User avatar
TheGurkha
Volunteer
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: Counting different entries with a "plus"

Post by TheGurkha »

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
ken johnson
Volunteer
Posts: 918
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: Counting different entries with a "plus"

Post by ken johnson »

This is an array formula and must therefore be entered using the Ctrl+Shift+Enter (PC keyboard) key combination...

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)
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.
Counting Different Entries array soln.ods
(10.98 KiB) Downloaded 293 times
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.
User avatar
JLeite
Posts: 31
Joined: Mon Jan 03, 2011 2:12 am
Location: Porto, Portugal

Re: [Solved] Counting different entries with a "plus"

Post by JLeite »

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
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-6ubuntu2~10.04.1
ken johnson
Volunteer
Posts: 918
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: [Solved] Counting different entries with a "plus"

Post by ken johnson »

This array formula avoids the problem of explicit column references...

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)
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.
User avatar
JLeite
Posts: 31
Joined: Mon Jan 03, 2011 2:12 am
Location: Porto, Portugal

Re: Counting different entries with a "plus"

Post by JLeite »

JLeite wrote:
ken johnson wrote:Assuming your expected result in A11, 5, should be 6 (|A|B|C|D|E|F| is 6 different letters), try...

Code: Select all

=IF(SUMPRODUCT(ISNUMBER(C2:M2))>0;SUM(C2:M2);SUMPRODUCT((C2:M2<>"")/(COUNTIF(C2:M2;C2:M2)+(C2:M2=""))))
in A1 filled down to A13.
If your 5 in A11 is correct then I am missing something.

Ken Johnson
Greetings.
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
Greetings.
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
ken johnson
Volunteer
Posts: 918
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: [Solved] Counting different entries with a "plus"

Post by ken johnson »

See if my explanation in the attached Writer doc makes any sense.
Formula explanation.odt
(20.12 KiB) Downloaded 193 times
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.
User avatar
JLeite
Posts: 31
Joined: Mon Jan 03, 2011 2:12 am
Location: Porto, Portugal

Re: [Solved] Counting different entries with a "plus"

Post by JLeite »

ken johnson wrote:See if my explanation in the attached Writer doc makes any sense.
Formula explanation.odt
Ken Johnson
Greetings, Ken.
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
Post Reply