Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

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
1  
ah, now I get it, I'm on it ;) –  Sentry Nov 22 '12 at 15:12
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.