Cara menggunakan google sheets dsum criteria

Sintaks

DSUM(database, field, criteria)

Sintaks fungsi DSUM memiliki argumen berikut:

• Database    Diperlukan. Rentang sel yang membentuk daftar atau database. Database adalah daftar dari data yang terkait di mana baris-baris informasi terkait adalah rekaman, dan kolom-kolom data adalah bidang. Baris pertama daftar tersebut berisi label untuk masing-masing kolom.

• Field    Diperlukan. Mengindikasikan kolom yang digunakan dalam fungsi tersebut. Masukkan label kolom yang dimasukkan di antara dua tanda kutip ganda, seperti "Umur" atau "Hasil," atau angka (tanpa tanda kutip) yang menyatakan posisi kolom di dalam daftar: 1 untuk kolom pertama, 2 untuk kolom kedua, dan seterusnya.

• Criteria    Diperlukan. Adalah rentang sel berisi syarat yang Anda tentukan. Anda dapat menggunakan rentang untuk argumen kriteria, selama meliputi setidaknya satu label kolom dan setidaknya satu sel di bawah label kolom di mana Anda menentukan kondisi untuk kolom tersebut.

Keterangan

• Anda dapat menggunakan rentang apa saja untuk argumen kriteria, selama meliputi setidaknya satu label kolom dan setidaknya satu sel di bawah label kolom di mana Anda menentukan kondisi untuk tersebut.

  Misalnya, jika rentang G1:G2 berisi label kolom Pendapatan dalam G1 dan jumlah $10.000 di G2, Anda dapat menetapkan rentang tersebut sebagai MatchIncome dan menggunakan nama itu sebagai argumen kriteria dalam fungsi database tersebut.

• Meskipun rentang kriteria dapat ditempatkan di mana pun pada lembar kerja, jangan tempatkan rentang kriteria di bawah daftar tersebut. Jika Anda menambahkan lebih banyak informasi ke daftar, informasi baru itu ditambahkan ke baris pertama di bawah daftar tersebut. Jika baris di bawah daftar tersebut tidak kosong, Microsoft Excel tidak dapat menambahkan informasi baru itu.

• Pastikan bahwa rentang kriteria tidak tumpang tindih dengan daftar.

• Untuk melakukan operasi di seluruh kolom dalam suatu database, masukkan baris kosong di bawah label kolom dalam rentang kriteria tersebut.

Contoh

Salin contoh data di dalam tabel berikut ini dan tempel ke dalam sel A lembar kerja Excel yang baru. Agar rumus menunjukkan hasil, pilih datanya, tekan F2, lalu tekan Enter. Jika perlu, Anda bisa menyesuaikan lebar kolom untuk melihat semua data.

Pohon

Tinggi

Umur

Hasil

Laba

Tinggi

="=Apel"

>10

<16

="=Pir"

Pohon

Tinggi

Umur

Hasil

Laba

Apel

18

20

14

$105

Pir

1.2

1.2

10

$96

Ceri

1,3

14

9

$105

Apel

14

15

10

$75

Pir

9

8

8

$77

Apel

8

9

6

$45

Rumus

Deskripsi

Hasil

=DSUM(A4:E10,"Profit",A1:A2)

Total profit dari pohon apel (baris 5, 8, dan 10).

$225

=DSUM(A4:E10,"Profit", A1:F3)

Total profit dari pohon apel dengan tinggi antara 10 dan 16 kaki, serta semua pohon pir (baris 6, 8, dan 9).

Here you can learn the use of DSUM function in Google Sheets with the help of a simple example and supporting pictures.

As its name suggests, you can use it (the DSUM function) to return the sum of values in any numeric field in a database, based on the criteria given.

DSUM is similar to another Google Sheets function called SUMIF/SUMIFS. Both can provide you with the same results.

But as far as I am concerned, SUMIF is more user-friendly. There is one major difference between DSUM and SUMIF/SUMIFS.

The former is a database function that would return the sum of values selected from a database table similar array/range using an SQL like query whereas the latter is of the logical type.

You may find more details here – Difference Between SUMIFS and DSUM in Google Sheets.

Let me elaborate on how to use the DSUM function in Google Sheets.

How to Use the DSUM Function in Google Sheets

Similar to my other Spreadsheet tutorials, you may want to first create a database, I mean a table, as below to learn/experiment my DSUM formula.

Try to maintain the data range reference B5:D13, then only you can quickly adapt to my formula explanation.

Now time to understand the DSUM function Syntax.

Google Sheets DSUM Function Syntax Explained (Also Using Picture)

Syntax:

DSUM( database, field, criteria )

The syntax arguments are explained below. To make you understand it clearly, I have also included one screenshot.

Arguments:

database – It’s the data range to consider. It must be structured in such a way that the first row contains the labels for each column’s values.

For example, you can call my above sample data as the “database”, i.e. the range B5:D13. The first row, i.e. B5:D5 contains the labels.

field – it indicates which column in the database contains the values to be extracted and operated on.

Here, in my example, I’ll use column 3 as the field which is the only numeric column in the array.

Regarding the use of ‘field’, you can directly enter the number 3 in the formula or put the column name within double quotes, here it is “Sales Value”.

criteria – the range containing conditions or criteria, it can be zero or more, by which to filter the database values before operating.

In DSUM the criteria should contain at least one column name and one cell below it. Better you copy-paste all the column labels from your database into another range to use as criteria. Then fill only the required ones.

I know it’s a bit confusing. So, I’ve tried to explain all of the arguments in Google Sheets DSUM function with the help of the below screenshot (example).

Database inside the red square (B5:D13), the field within the green square (3 or “Sales Value”), and criteria within the cyan square (only B2:C3, in D3 we will insert our formula).

The argument ‘criteria’ detailed below.

Examples

I hope you are ready with the sample ‘database’ as above in the range B5:D13. Now we only need to specify the criteria (already there on the screenshot above) which to be used in the function. How to set the criteria?

Copy the labels “Name of Sales Person”, “Area” and “Sales Value” and paste the same into B2:D2.

What we are going to do is we are totaling the Sales Value of the person “Myron Ambriz” in area “North”.

For testing, you should enter the name “Myron Ambriz” in cell B3 and “North” in cell C3. So our criteria will look as follows.

Then enter the below DSUM formula in cell D3. Of course, it contains multiple criteria.

=dsum(B5:D13,3,B2:C3)

In this formula, B5:D13 is our ‘database’, 3 is the ‘field’ (the field/column to sum), and B2:C3 is the ‘criteria’ range.

If you insert the above formula in cell D3, you would get the result as $1,000.

Now let’s expand the criteria as below and see the result.

Formula:

=dsum(B5:D13,3,B2:C4)

Result: $3,000.

This way you can use DSUM in Google Sheets as well as in Microsoft Excel.

Can I Use DSUM Criterion Inside the Formula in Google Sheets?

Yes, you can! But not like the way we deal with SUMIF or SUMIFS criterion.

Just check the last formula criterion which is in the criteria range B2:C4 above. It can be replaced with the below criterion inside the formula.

{{"Name of Sales Person";"Myron Ambriz";"Myron Ambriz"},{"Area";"North";"South"}}

The formula will be;

=dsum(B5:D13,3,{{"Name of Sales Person";"Myron Ambriz";"Myron Ambriz"},{"Area";"North";"South"}})

The following tutorial gives more insight into the use of multiple conditions in the DSUM function – AND, OR in Multiple Criteria DSUM in Google Sheets (Within Formula).

Apa saja fitur utama dari Google Sheets?

Jenis fitur apa yang bisa kamu gunakan di Google Sheets untuk meringkas data dalam jumlah besar dengan cepat?

Rumus Google Sheet dimana?