Re: [libreoffice-users] Re: To sum only those values in a range that are between two number

Hi Pál,

csanyipal schrieb:

> So if I understand well, SUMPRODUCT in this case will summarize values of
> cells in an array if we gives the formula:
> =SUMPRODUCT('O.tanácsi f.év'.Z7:Z26;'O.tanácsi f.év'.Z7:Z26>=8;'O.tanácsi
> f.év'.Z7:Z26<=17)
>
> *=SUMPRODUCT(array;condition1;condition2)*
>
> but will only count cells with the given conditions if we gives the formula:
> =SUMPRODUCT('O.tanácsi f.év'.Z7:Z26>=8;'O.tanácsi f.év'.Z7:Z26<=17)
>
> * =SUMPRODUCT(condition1;condition2)*
>
> condition1 and condition2 are arrays too.
>
> As one can see in the LO Help,  SUMPRODUCT multiplies corresponding elements
> in the given arrays, and returns the sum of those products.
>
> I'm trying to understand SUMPRODUCT considering this case abowe. Is there
> something more to be added to this explanation?

Yes. The part 'O.tanácsi f.év'.Z7:Z26>=8 results in an internal array of 
boolean and in LibreOffice "boolean" is not an own data type, but it is 
used as number with TRUE = 1 and FALSE = 0. Therefore the product table
first operand   second operand   product
1 (=TRUE)       1 (=TRUE)        1 (=TRUE)
1 (=TRUE)       0 (=FALSE)       0 (=FALSE)
0 (=FALSE)      1 (=TRUE)        0 (=FALSE)
0 (=FALSE)      0 (=FALSE)       0 (=FALSE)
is the table of logical AND at the same time.
And because multiplying with 0 results in 0 only those entries, which 
are multiplied with 1, are "count" in the summing up.

Kind regards
Regina  



--
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