2

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.

1 Answer 1

2

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

4
  • 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, 2012 at 15:10
  • 1
    ah, now I get it, I'm on it ;)
    – Sentry
    Nov 22, 2012 at 15:12
  • 1
    I corrected it, does that work now?
    – Sentry
    Nov 22, 2012 at 15:35
  • How about a kiss ? <3 <3 <3
    – ychaouche
    Nov 22, 2012 at 16:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .