excel - Challange with DSUM Function: How can I use the DSUM function to sum values based on criteria that match the starting ch

I am facing some challang in summing up values using DSUM function in Excel. Below are the detils.

I have a table:

ID    Region  Item      Category    Quantity      Price   Stock
===================================================================
1   North   Pen     Office      435     15  1024
2   South   Pencil      School      139     11  1614
3   East    Eraser      Office      91      23  640
4   West    Notebook    School      256     49  1702
5   Central Marker      Office      375     36  1540
6   West    Pen     School      423     22  860
7   West    Pencil      School      339     12  1954
8   West    Eraser      School      298     16  1507
9   North   Notebook    Office      321     45  688
10  West    Marker      School      473     29  1934

And, I am calculating Sum of "Stock" where Item is "Pen"

Criteria:

Item
Pen

I am facing some challang in summing up values using DSUM function in Excel. Below are the detils.

I have a table:

ID    Region  Item      Category    Quantity      Price   Stock
===================================================================
1   North   Pen     Office      435     15  1024
2   South   Pencil      School      139     11  1614
3   East    Eraser      Office      91      23  640
4   West    Notebook    School      256     49  1702
5   Central Marker      Office      375     36  1540
6   West    Pen     School      423     22  860
7   West    Pencil      School      339     12  1954
8   West    Eraser      School      298     16  1507
9   North   Notebook    Office      321     45  688
10  West    Marker      School      473     29  1934

And, I am calculating Sum of "Stock" where Item is "Pen"

Criteria:

Item
Pen

=DSUM($A$1:$G$11, "Stock", $I$3:$I$4)

Result: 5452 (Wrong)

• Note: DSUM function adding all the "Stock" for "Pen" and "Pencil" or I can say this is adding all the "Stock" with criteria starting with "Pen".

Correct Result is: 1884

Can you please check this issue or guide me on the same.

I Tried summing up Stock values as per Item

• excel
• excel-formula
• excel-365

Share Improve this question Follow edited 11 hours ago Black cat 5,90244 gold badges1717 silver badges3535 bronze badges asked 15 hours ago Annu RanaAnnu Rana 1

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Use DSUM() like-

=DSUM(A1:G11,"Stock",I1:I2)

In criteria range if you want equal then put Pen like ="=Pen" so it will appear =Pen.

And obviously, you can use SUMIFS(), FILTER() then SUM(), GROUPBY() function to achieve same result. Here is SUMIFS().

=SUMIFS(G2:G11,C2:C11,"Pen")