Am 07.10.2012 12:28, Alaak wrote: > Hi, > > I have the following problem. I need to count all rows from a table > satisfying two conditions in separate columns. I know of the function > COUNTIF (ZÄHLENWENN in german), but that one is only able to consider > one condition. Let's look at a brief example. > > s1 s2 > a true > b true > a false > a true > b false > > I would like to count all rows having the value "a" in s1 and "true" in > s2. Using COUNTIF I could either count on a condition on s1 or s2 but > not on both. Any ideas on how to solve this problem? > > Thanks and regards >
Counting all true "a" values =SUMPRODUCT($A$2:$A$999="a" ; $B$2:$B$999=1) [assuming that the true/false are numeric 1/0 values] If the above assumption is true, you could also use =SUMIF($A$2:$A$999 ; "a" ; $B$2:$B$999) [each true value equals 1] For the false "a" values: =COUNTIF($A$2:$A$999 ; "a") - SUMIF($A$2:$A$999 ; "a" ; $B$2:$B$999) [all "a" minus the true "a"] If the true/false values are text: =SUMPRODUCT($A$2:$A$999="a" ; $B$2:$B$999="true") German name of SUMPRODUCT is SUMMENPRODUKT. -- For unsubscribe instructions e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
