In an earlier tutorial, that about multiple criteria use in DSUM, I have promised to share with my readers about the use of multiple sum columns in DSUM.
Multiple sum columns mean how to use multiple fields, I’m referring to the argument ‘field’, in DSUM.
Syntax: DSUM(database, field, criteria)
If you know how to use the DSUM formula, then things will be easy for you. For that, first, you can go through my below DSUM function guide.
Must Check: How to Use the DSUM Function in Google Sheets.
Introduction
DSUM is a database Sum function, ‘D’ in DSUM stands for database, which works well with a structured table in Google Sheets and as well as in Excel.
On the contrary, the functions like SUMIF, SUMIFS, or SUMPRODUCT don’t require a structured table. You can just use a data range in these functions.
Understand what is a structured data table suitable for DSUM and what is a data range or dataset.
Multiple Sum Columns in DSUM Function in Google Sheets
In order to
=sum(
ArrayFormula(
dsum(database,{field 1,field 2,field 3,...},criteria)
)
)
In this field 1, field 2, field 3,… are the multiple columns. Also please note the use of the SUM and ArrayFormula functions.
In short, include fields (sum columns), that separated by a comma, within Curly Brackets and wrap the DSUM with ArrayFormula then Sum.
Here is one real-life example for the use of the multiple DSUM columns in Google Sheets.
Criteria within Multi-field DSUM
Formula:
=sum(
ArrayFormula(
dsum(A1:E7,{3,4,5},{"name";"Student 1"})
)
)
When you closely analyze this formula, you can understand one thing.
The DSUM formula conditionally sums multiple column range, i.e. the range C2:E7 (column 3, 4, and 5). These columns are known as fields in DSUM.
Then what about the condition/criterion used?
The formula sums the fields “maths”, “physics”, “chemistry” if the field “name” contains “Student 1”. The formula sums the values in the shaded area (please refer to the above screen capture).
If you prefer to use the above DSUM criteria {"name";"Student 1"}
as cell reference, then follow the below tips.
‘D’ Function Criteria as Range (Array)
Simply insert the criterion label “name” in cell G1 and criterion “Student 1” in cell G2 (both strings without quotes) then use the following DSUM.
=sum(
ArrayFormula(
dsum(A1:E7,{3,4,5},G1:G2)
)
)
This way you can use multiple sum columns in DSUM function in Google Sheets.
Conclusion
In similar scenarios, you can use alternative functions like Sumif, Sumproduct, Query, etc. Here is that tutorial – How to Include Multiple Sum Columns in SUMIF in Google Sheets (formula options).
After learning the use of multiple column DSUM in Google Sheets, read the below two tutorials to get some advanced tips on ‘D’ functions.
Resources: