DSUM with multiple criteria

Question

I'm trying to get the sum of all items in column F:F when Column J:J = "Channel"and Column K:K = "Country"

=DSUM(Sheet1!$A$1:$K$142,Sheet1!$F$2:$F$142,{{Sheet1!K:K;"Channel"},{Sheet1!L:L;"Country"}})

The above query returns a 0 when it should return a 7.

Answer 1

Try:

=DSUM(Sheet1!$A$1:$L$142, Sheet1!$F$1, {{Sheet1!$K$1; "Channel"}, {Sheet1!$L$1; "Country"}})

Notice changes about the parameters:

1. Sheet1!$A$1:$L$142 column L:L should be included if you want to use it in your criteria.
2. Second parameter just needs a column name (Sheet1!$F$1) or its number (6). No need to place the whole range there.
3. You need to have a two strings range for criteria: first one with the column name, second with the criteria for that column. You set before the whole column K:K and L:L and placed a string to match at the bottom. Only the first tow rows were used, so you criteria was actually like this: kolumn K:K should be like the value in K2 and column L:L like the value in L2 (but this one didn't actually worked as this column was not a part of your table (the 1st parameter)).

Or you can use SUMIFS:

=SUMIFS(Sheet1!$F$2:$F$142, Sheet1!$K$2:$K$142, "Channel", Sheet1!$L$2:$L$142, "Country")

Or if you need to sum the whole column (not just down to 142 row):

=SUMIFS(Sheet1!$F:$F, Sheet1!$K:$K, "Channel", Sheet1!$L:$L, "Country")

Answer 2

Here's a basic proof-of-concept.

Count all vegetables

• Answer: 18
• Formula: =DSUM(A4:D10,"Qty",{"Category";"Vegetable"})

Count all vegetables at Safeway

• Answer: 10
• Formula: =DSUM(A4:D10,"Qty",{{"Category";"Vegetable"},{"Store";"Safeway"}})