[libreoffice-users] Re: Count number of rows satisfying two conditions

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: users+help@global.libreoffice.org
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