Count rows with specific conditions

[simon.pat]

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]

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]

The best I could come up with was this:

[erikanderson3]

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:

[erikanderson3]

Found it!

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

[simon.pat]

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]

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]

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]

Fabulous! Thanks for that.

Cheers,

Erik

[Guest]

This sum thing works great on arrays.

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

Thanks,

Joel

[Guest]

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]

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]

ok, I thought that was obvious.

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

8-p