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

[ychaouche]

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"))

With additions

=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.

[qubit1]

Hi @ychaouche,

Have you found a solution to your question?

What file format are you using here? Modern versions of Excel can (at least in theory) use ODF 1.2; hopefully both Calc and Excel can interpret formulae in ODF 1.2 docs in the same fashion!

[ychaouche]

Yes, sorry for late reply. I came back here to check for another question and bounced into this See my own answer.

[ychaouche]

Solution was given on superuser : I had to do a product of two SUMPRODUCT conditions, like so :

=SUMPRODUCT( COND 1 * COND 2)

My example :

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

Otherwise, if a row had two 'TRUE’s, it would be counted twice and if it had one TRUE and one FALSE it would still be counted once (instead of being discarded, because I want to count only those rows for which both conditions are TRUE).