Re: [libreoffice-users] how to use sumif with multiple condition and refer to cell in formula

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 <nagy.akos@codespring.ro>
> To: <users@global.libreoffice.org>
> Subject:             [libreoffice-users] how to use sumif with 
> multiple condition and refer to cell in formula
> Send reply to:   users@global.libreoffice.org
>
> > 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:
> > 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


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