I’m trying to use multiple criteria in a DSUM function in Google Sheets but I’m stuck. Here’s what I’ve got so far:
=DSUM(A1:C50, "Amount", {"Type";"Expense";"Approved";"Yes"})
This doesn’t work as expected. It only uses the first criterion (“Type”;“Expense”) and ignores the second one (“Approved”;“Yes”).
I know I can use a separate table for criteria, like this:
=DSUM(A1:C50, "Amount", F1:G2)
But I’d rather not create extra tables. Is there a way to include multiple criteria directly in the DSUM function? Any help would be great!
hey neo_stars, i feel ur pain! i had the same issue b4. have u tried using SUMIFS instead? it’s way easier for multiple criteria. like this:
=SUMIFS(C1:C50, A1:A50, “Expense”, B1:B50, “Yes”)
where C is amount, A is type, and B is approved. no extra tables needed!
While SUMIFS and QUERY are great alternatives, there’s actually a way to use multiple criteria directly in DSUM without extra tables. The trick is to use an array formula. Try this:
=DSUM(A1:C50, “Amount”, {“Type”,“Approved”;“Expense”,“Yes”})
Make sure to press Ctrl+Shift+Enter (or Cmd+Shift+Enter on Mac) to enter it as an array formula. This structure allows you to specify multiple criteria in a single function call.
If you need more than two criteria, just expand the array accordingly. For example:
=DSUM(A1:D50, “Amount”, {“Type”,“Approved”,“Category”;“Expense”,“Yes”,“Travel”})
This method keeps your formula concise and avoids the need for helper columns or separate criteria tables.
I’ve run into this issue before, and I found a workaround that might help you. Instead of trying to use multiple criteria directly in DSUM, you can nest your DSUM function within a QUERY function. Here’s an example of how you could structure it:
=QUERY(A1:C50, “SELECT SUM(C) WHERE A = ‘Expense’ AND B = ‘Yes’”)
This assumes ‘Type’ is in column A, ‘Approved’ is in column B, and ‘Amount’ is in column C. You can adjust the column references as needed.
The QUERY function is pretty powerful and flexible, allowing you to apply multiple criteria without creating separate tables. It might take some getting used to if you’re more familiar with DSUM, but it’s worth learning for situations like this.
Just make sure your column headers are in the first row of your data range. If they’re not, you’ll need to add a ‘LABEL’ clause to your QUERY to name the summed column.