2012 Archives by date, by thread · List index

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

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