# How to count how many rows contain particular values in a particular set of cells in libreoffice and excel

This Calc file : http://dropcanvas.com/521xc/107 have a sheet called "Etat" and a sheet called "Total".

In Etat.F20 I have a formula that counts how many rows in Total have the value "Bug" in the D column AND the value "Basse" in the E column.

I'm doing that with the help of this formula :

`=SUMPRODUCT(\$Total.D\$1:D\$1048576="Bug",\$Total.E\$1:E\$1048576="Basse")`

This works fine in LibreOffice, but not in Excel. In Excel, I was told to use N() around the logical comparisons, to get a value out of it, because Excel can't evaluate logical expressions inside SUMPRODUCT formulas.

`=SOMMEPROD(N(Total!D:D="Bug");N(Total!E:E="Basse"))`

Which works great, except not in Libreoffice :'(

So I now have two different formulas, one for libreoffice Calc and one for MS Excel.

What I want is just one formula that would work on both softwares. Any ideas ?

I tried various variations of the Excel formula, but none worked in libreoffice Calc

With comas

`=SUMPRODUCT(N(\$Total.D\$1:D\$1048576="Bug"),N(\$Total.E\$1:E\$1048576="Basse"))`

`=SUMPRODUCT(N(\$Total.D\$1:D\$1048576="Bug") * N(\$Total.E\$1:E\$1048576="Basse"))`

With multiplications

`=SUMPRODUCT(N(\$Total.D\$1:D\$1048576="Bug") + N(\$Total.E\$1:E\$1048576="Basse"))`

PS : the original file was made on excel with COUNTIFS instead of SUMPRODUCT, but COUNTIFS isn't present in Libreoffice. Someone showed me the SUMPRODUCT trick but as you can see I'm not sure I'm using it correctly.

-

What works for me in LibreOffice:

=SUMPRODUCT((\$Total.D\$1:D\$1048576="Bug")*(\$Total.E\$1:E\$1048576="Basse"))

I don't know about Excel, but I found claims on the net that the "*" is forcing Excel to interpret the evaluation of the condition as number.

You might have to tell LibreOffice to accept the english names for the formulas:

Extras -> Options -> LibreOffice Calc -> Formula -> Use english function names

-
Yeah, the problem is that i want rows that have both conditions true simultaneously. Using two sepearate countif(s) would show 2 rows instead of 1 when one row have both conditions true, and 1 (instead of 0) when only one condition is true. – ychaouche Nov 22 '12 at 15:10
ah, now I get it, I'm on it ;) – Sentry Nov 22 '12 at 15:12
I corrected it, does that work now? – Sentry Nov 22 '12 at 15:35
How about a kiss ? <3 <3 <3 – ychaouche Nov 22 '12 at 16:55