OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Count rows with specific conditions

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
simon.pat
Newbie
Newbie


Joined: 07 Apr 2004
Posts: 4

PostPosted: Wed Apr 07, 2004 4:26 am    Post subject: Count rows with specific conditions Reply with quote

Hello,
I tried to count the number of rows containing specific values. My file look like the following(Column A and B):
A B
Toto Titi
Tata Titi
Toto Tata
Toto Titi
Toto Tata

I want to know how many rows have Toto in column A AND Titi in column B.
I tried {=COUNT(IF(AND($A$1:$A$5="Toto";B$1:B$5="Titi")))} but it returning 1.
Thank you very much for your help.
Back to top
View user's profile Send private message
MIFA
General User
General User


Joined: 01 Apr 2004
Posts: 7
Location: Wageningen Netherlands

PostPosted: Wed Apr 07, 2004 3:14 pm    Post subject: Reply with quote

if you want to make an extra collumn it aint that hard.
make a new collumn c where the function in c1 is =if(and(A1="Toto";B1="Titi");1;0) where c2 is =if(and(A2="Toto";B2="Titi");1;0) and so on
now you can yust sum up this collumn because if true then the result is 1.
But i can imagine you also want the number of rows witch contains tata en titi
now you need an extra collumn

i also cant figure it out with a count function

good luck,

Michiel
Back to top
View user's profile Send private message Send e-mail Visit poster's website
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Wed Apr 07, 2004 6:34 pm    Post subject: Reply with quote

The best I could come up with was this:
Code:
=SUM(AND(A1="Toto";B1="Titi")+AND(A2="Toto";B2="Titi")+AND(A3="Toto";B3="Titi") +AND(A4="Toto";B4="Titi")+AND(A5="Toto";B5="Titi"))
which is butt ugly at best, though at least it comes up with the right number (2). Looking into the Calc help docs suggests that AND() cannot evaluate ranges for text equivalency the way you were trying to use it:
Calc Help wrote:
If an argument entered as am array contains text or empty cells, these will be ignored. For example, formula =AND(0<C9:C16; C9:C16<10) returns TRUE if array (C9:C16) contains values between 0 and 10, and cells containing text or empty cells may be included.
If the range indicated does not contain any logical values, AND returns the error value #VALUE!


When I plugged your COUNT() formula into Calc, I got #VALUE! Which makes me want to ask, what version of OOo are you using? I'm confused that you didn't get the same error message. I've got 1.1.1.

Anyway, I'll see what else I can dig up that might be more elegant than having to specify every single cell pair.

Cheers,

Erik
Back to top
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Wed Apr 07, 2004 7:35 pm    Post subject: Reply with quote

Have you tried messing around with the Data Pilot? I don't know if it fits with your project, but I found that it can do just what you're looking for, without too much trouble. And it scales much more nicely than the SUM() example I gave previously.

You'll need column headings. Do this by inserting a row above the first line of "Toto" and "Titi". Enter what you will; I just used "A" and "B".

Now select the whole range you'll want to work with. In this example, A1:B6. Next click on Data -> DataPilot -> Start. In the Select Source dialog, choose "Current selection" and click OK.

Things get a little complicated here, and it took me a bit to find out how to make it work. You'll see three white boxes marked Column, Row, and Data, with the two column headings A and B as buttons to the right of the Column field. Drag A and B to the Data field, then double-click each and select Count. Click on the More>>> button if you need to specify an empty space on your sheet for the results, and click OK.

Now you should see something like:
Code:
Filter

Data
Count - A            5
Count - B            5
Total Count - A      5
Total Count - B      5

Click on the Filter button. In the Filter dialog, under "Field name" choose "A", leave Condition as "=", and choose Toto in the Value dropdown (if you type it in with quotes, it doesn't work). Then under the top Operator box, choose AND, and then "B", "=", and "Titi". Click on OK, and the DataPilot chart should show the correct count of 2.

Now I'm sure there's a better way to use the DataPilot, and this is just a quick-and-dirty experiment, but it seems to work without having to delve into writing macros.

HTH,

Erik
Back to top
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Wed Apr 07, 2004 10:05 pm    Post subject: Reply with quote

Found it! Cool

What you need is DCOUNT(). You'll still need to put in a header row, but this works out much better:
Code:
=DCOUNT(A1:B6;A1:B6;A1:B2)

This function pretends a section of your spreadsheet is a database. The first parameter defines the whole database, the second defines which columns of the database to search through, and the last defines which headings and values to look for. Have a look at the DCOUNT() explanation in the Calc help file for further details.

Cheers,

Erik
Back to top
View user's profile Send private message
simon.pat
Newbie
Newbie


Joined: 07 Apr 2004
Posts: 4

PostPosted: Wed Apr 07, 2004 10:31 pm    Post subject: Reply with quote

Thank you very much for your help. Very Happy

I just found the solutions using an array :
{=SUM((A1:A500="Toto")*(B1:B500="Titi")*1)}
This solution is very useful for a big number of rows without using the header row as with DCOUNT.
Cheers
Back to top
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Wed Apr 07, 2004 10:37 pm    Post subject: Reply with quote

Simon,

I just tried your formula, and it's giving me 0 (zero), both with and without the heading row. Chopping the ranges down from 500 to 5 then gives me the error message #VALUE!

Question Question

Erik
Back to top
View user's profile Send private message
simon.pat
Newbie
Newbie


Joined: 07 Apr 2004
Posts: 4

PostPosted: Wed Apr 07, 2004 10:47 pm    Post subject: Reply with quote

Erik,
Sorry I forgot to say that you have to click on the "Auto Pilot: Function" button. Then on the Structure pan and then check the Array box. It will automatically add the {} and transform the function into an array function.

Simon
Back to top
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Wed Apr 07, 2004 10:51 pm    Post subject: Reply with quote

Fabulous! Thanks for that. Very Happy

Cheers,

Erik
Back to top
View user's profile Send private message
Guest






PostPosted: Thu Apr 29, 2004 6:26 pm    Post subject: Reply with quote

This sum thing works great on arrays.

Could someone tell me where in the starcalc documentation this jewel is documented?

Thanks,

Joel
Back to top
Guest






PostPosted: Mon May 03, 2004 12:41 am    Post subject: Reply with quote

Why not using the function COUNTIF?

=COUNTIF(B2:B8;"Titi")

PS: look through the all available functions. You will be amazed what can be found there Wink[/b]
Back to top
simon.pat
Newbie
Newbie


Joined: 07 Apr 2004
Posts: 4

PostPosted: Mon May 03, 2004 12:49 am    Post subject: Reply with quote

Becaue it is not working in that case Wink .....
COUNTIF works well if you just have a condition on one column. In my case there is a condition on each column....
Back to top
View user's profile Send private message
Guest






PostPosted: Mon May 03, 2004 12:57 am    Post subject: Reply with quote

ok, I thought that was obvious. Wink

You can do this like =COUNTIF(C1:C19; "TotoTiti") while the C column is =A1&B1

8-p
Back to top
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group