Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This Calc file : 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 :


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.


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


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.

share|improve this question
up vote 2 down vote accepted

What works for me in LibreOffice:


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

share|improve this answer
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

You must log in to answer this question.

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