Google Docs using multiple manual criterion in DSUM

Question

How can I manually enter multiple criterion into a DSUM function?

I can have it check a single set of criterion with: =DSUM(J3:L55, "Charge", {"Category";"Coffee"})

However changing that to =DSUM(J3:L55, "Charge", {"Category";"Coffee";"Split";"Yes"} Causes it to just use the "Category";"Coffee" but and ignore the ;"Split";"Yes" section.

What is the syntax to set multiple criterion in google docs? I cannot really make a 2x2 table to each category I have (=DSUM(J3:L55, "Charge", D7:E8) ) and instead need to just manually enter the criterion.

DSUM with table criterion is in blue

DSUM with table criterion is in blue. I am selecting "Category" though "Split?" and want to use both Category and Split as criterion without having to resort to the darker blue table you see there

score 24 · Accepted Answer · edited Oct 16 '15 at 12:44

24

Try

=DSUM(J3:L55, "Charge", {{"Category";"Coffee"},{"Split";"Yes"}})

edited Oct 16 '15 at 12:44

Jasper

7,031
3
35
43

answered Oct 16 '15 at 12:29

wim1969

282
2
2

Not sure why you were downvoted. This is the correct syntax. This is a really old question and I have long since figured out how to do this, just on a different spreadsheet. – Douglas Gaskell Oct 20 '15 at 01:43
1

I downvoted because this answer doesn't work in all languages. The other syntax pointed out in other answers (using `\`) works in every language. – LucasB Sep 25 '17 at 19:17
5

In portuguese, testing and failing with the answers below, I got it to work like this: `=DSUM(J3:L55; "Charge"; {{"Category";"Coffee"}\{"Split";"Yes"}})` May work in other latin languages. – AnaRita Nov 06 '17 at 12:04
User @Keybonesabi is right. Do NOT use this notation, instead use his. – aefxx Jan 10 '19 at 00:06
What's the difference between the `;` and the `,` ? – CodyBugstein Mar 01 '19 at 03:58
Cody, in the old method ';' Separated the test titles from the associated values (Rows) ',' Separated the titles and test values (Columns) =DSUM(TestTitle1,TestTitle2;TestValue1,TestValue2) – Keybonesabi Jun 28 '19 at 17:21

Keybonesabi · Answer 2 · 2019-03-08T17:45:08.337

Google updated the DSUM function. The update requires you to specify an array/table for criterion.
Where Column names and Criterion were named before, you now reference a table:
{F3,G4}
Where
"F3" and "G3" are the column names to be referenced.
"F4" and "G4" are the Test Values.
"F3" is Category
"G3" is Split?
"F4" is Coffee
"G4" is Yes
An example of the new formula is:
=DSUM(A1:E55, "Charge", {F3:G4})
I think this makes it easier to update the table and reference changes.
You can have multiple criteria, I tested it with "Equipment" and "Yes" by adding another row to my criterion table.

Reference: Google Help: DSUM()

The Old Way:
The correct syntax is to use commas to separate the columns.
{"Category","Split";"Coffee","Yes"}
{ Column1 , Column2 ; Test 1 , Test 2}
so your formula should be
=DSUM(J3:L55, "Charge", {"Category","Split";"Coffee","Yes"})

After reviewing my code, it appears there is a change to how Google Sheets runs DSUM. — Keybonesabi, Mar 08 '19 at 17:13

score 8 · Answer 3 · edited Jan 14 '20 at 02:11

8

In languages that use , as the decimal separator, you cannot use , for separating values. Use a backslash \ instead. So your formula should then be

=DSUM(J3:L55; "Charge"; {"Category"\"Split";"Coffee"\"Yes"})

edited Jan 14 '20 at 02:11

Felix Dombek

13,664
17
79
131

answered Jun 18 '15 at 15:55

UJK

89
1
3

1

Reviewer's comment: That seems a great first answer, although I don't see the relation between your solution and Turkish ... – Frederic Adda Jun 18 '15 at 15:58
3

my answer is for Turkish users, because in Turkish format of google spreadsheets, you can't use "," char as separator in formulas. It is used for decimal point, so google recommend using "\" char instead of "," – UJK Jun 18 '15 at 20:01

score 5 · Answer 4 · edited Dec 19 '15 at 17:53

5

Same if you are using French format. You can not use ,. Your formula should be

=DSUM(J3:L55; "Charge"; {"Category"\"Split";"Coffee"\"Yes"})

Maybe the same for other Latin laguages too

edited Dec 19 '15 at 17:53

bish

3,381
9
48
69

answered Dec 19 '15 at 15:24

nolcanot

51
1
1

score 0 · Answer 5 · answered Dec 04 '14 at 19:04

When you do d7:e8, it'll iterate on columns first and lines later. So on your example, you would have "Category", "Split?" (1st line) and "Coffee","Yes" (2nd line).

So, instead of {"Category";"Coffee";"Split";"Yes"} you should use {"Category";"Split";"Coffee";"Yes"}.

Google Docs using multiple manual criterion in DSUM

5 Answers5