[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
simon.pat
Newbie

Joined: 07 Apr 2004
Posts: 4

 Posted: Wed Apr 07, 2004 4:26 am    Post subject: Count rows with specific conditions 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.
MIFA
General User

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

 Posted: Wed Apr 07, 2004 3:14 pm    Post subject: 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
erikanderson3

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

Posted: Wed Apr 07, 2004 6:34 pm    Post subject:

 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

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
erikanderson3

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

Posted: Wed Apr 07, 2004 7:35 pm    Post subject:

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
erikanderson3

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

Posted: Wed Apr 07, 2004 10:05 pm    Post subject:

Found it!

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
simon.pat
Newbie

Joined: 07 Apr 2004
Posts: 4

 Posted: Wed Apr 07, 2004 10:31 pm    Post subject: Thank you very much for your help. 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
erikanderson3

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

 Posted: Wed Apr 07, 2004 10:37 pm    Post subject: 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! Erik
simon.pat
Newbie

Joined: 07 Apr 2004
Posts: 4

 Posted: Wed Apr 07, 2004 10:47 pm    Post subject: 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
erikanderson3

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

 Posted: Wed Apr 07, 2004 10:51 pm    Post subject: Fabulous! Thanks for that. Cheers, Erik
Guest

 Posted: Thu Apr 29, 2004 6:26 pm    Post subject: This sum thing works great on arrays. Could someone tell me where in the starcalc documentation this jewel is documented? Thanks, Joel
Guest

 Posted: Mon May 03, 2004 12:41 am    Post subject: 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 [/b]
simon.pat
Newbie

Joined: 07 Apr 2004
Posts: 4

 Posted: Mon May 03, 2004 12:49 am    Post subject: Becaue it is not working in that case ..... COUNTIF works well if you just have a condition on one column. In my case there is a condition on each column....
Guest

 Posted: Mon May 03, 2004 12:57 am    Post subject: ok, I thought that was obvious. You can do this like =COUNTIF(C1:C19; "TotoTiti") while the C column is =A1&B1 8-p
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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