Thanks for your answer. This resolve my first problem and is a very
useful formula, probably I can use it in many situation.
When I search for DSUM formula descriptions I found an another solution:
=SUM( (A1:A7 = 3)*(B1:B7 = "a") * C1:C7)
After you enter the formula, need to press Ctrl-Shift-Enter, because is
an array expression.
2012.01.09. 17:39 keltezéssel, Michael D. Setzer II írta:
On 9 Jan 2012 at 16:47, Nagy Ákos wrote:
Date sent: Mon, 9 Jan 2012 16:47:33 +0200
From: Nagy Ákos <[email protected]>
To: <[email protected]>
Subject: [libreoffice-users] how to use sumif with
multiple condition and refer to cell in formula
Send reply to: [email protected]
> Hi,
>
> I have some, I think not to simple problem in Calc.
>
> I have this table:
> A B C
> 3 a 1
> 4 b 1
> 5 b 0
> 7 a 0
> 5 a 1
> 7 b 0
> 0 b 1
>
> How can I totalize in one formula (without use a D column) the values
> in column A, only when the column B="a" and the C=1?
>
> And the another question, which resolve the first problem too, how can
> I refer to a test cell the example in sumif formula: The correct
> formula: =SUMIF(C1:C7;1;A1:A7)
>
> What I want:
> =SUMIF(C1:C7;*Cx*+3=1;A1:A7)
>
> where Cx is actually examined cell (C1, C2, C3, ...)
>
> or in this example:
> A B C
> 3 1 1
> 4 0 1
> 5 1 0
> 7 0 0
> 5 1 1
> 7 0 0
> 0 0 1
>
> =SUMIF(C1:C7;*AND(Bx, Cx)*;A1:A7)
> when Bx=1 and Cx=1, I want to totalize the values in A column.
>
>
The solution that I come up using dsum instead of sumif.
Put your values in A1:C8
In A9
=DSUM(A1:C8,"A", G1:H2)
The Criteria is in G1 thru H2.
G1: B
H1: C
G2: a
H2: 1
The DSUM allows for multiple criterias.
> --
> Nagy Ákos
>
>
>
> --
> 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
--
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
