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

5,444

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:
5,444

Related videos on Youtube

ychaouche
Author by

ychaouche

Updated on September 18, 2022

Comments

  • ychaouche
    ychaouche over 1 year

    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.

  • ychaouche
    ychaouche over 11 years
    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.
  • Sentry
    Sentry over 11 years
    ah, now I get it, I'm on it ;)
  • Sentry
    Sentry over 11 years
    I corrected it, does that work now?
  • ychaouche
    ychaouche over 11 years
    How about a kiss ? <3 <3 <3