Data Base Function in Excel

 

Data Base Function

 

Dsum Function

 

=DSUM(A5:E12,C6,A1:A2)

 

	A	B	C	D	E
1	Item	Qty.
2	Mouse	21
3
4
5	Item	Company	Qty.	Price	Amount
6	Mouse	Intex	5	250	1250
7	Keyboard	Hp	4	400	1600
8	Mouse	Intex	2	260	520
90	Keyboard	Hp	3	420	1260
10	Mouse	Intex	8	280	2240
11	Keyboard	Hp	5	300	1500
12	Mouse	Intex	6	230	1380

 

=DSUM(A5:E12,C5,A1:B2)

 

	A	B	C	D	E
1	Item	Company	Qty.
2	Keyboard	Hp	12
3
4
5	Item	Company	Qty.	Price	Amount
6	Mouse	Intex	5	250	1250
7	Keyboard	Hp	4	400	1600
8	Mouse	Intex	2	260	520
9	Keyboard	Hp	3	420	1260
10	Mouse	Intex	8	280	2240
11	Keyboard	Hp	5	300	1500
12	Mouse	Hp	6	230	1380

 

Daverage Function

 

=DAVERAGE(A5:E12,E5,A1:B2)

 

	A	B	C	D	E
1	Item	Company	Average Amount
2	Keyboard	Intex	1260
3
4
5	Item	Company	Qty.	Price	Amount
6	Mouse	Intex	5	250	1250
7	Keyboard	Hp	4	400	1600
8	Mouse	Intex	2	260	520
9	Keyboard	Intex	3	420	1260
10	Mouse	Intex	8	280	2240
11	Keyboard	Hp	5	300	1500
12	Mouse	Hp	6	230	1380

 

Dcount Function

 

=DCOUNT(A1:E12,C5,A1:B2)

 

	A	B	C	D	E
1	Item	Company	Total Record
2	Keyboard	Hp	2
3
4
5	Item	Company	Qty.	Price	Amount
6	Mouse	Intex	5	250	1250
7	Keyboard	Hp	4	400	1600
8	Mouse	Intex	2	260	520
9	Keyboard	Intex	3	420	1260
10	Mouse	Intex	8	280	2240
11	Keyboard	Hp	5	300	1500
12	Mouse	Hp	6	230	1380

 

Dcounta Function

 

=DCOUNTA(A5:E12,C5,A1:B2)

 

	A	B	C	D	E
1	Item	Company	Total Record
2	Keyboard	Intex	1
3
4
5	Item	Company	Qty.	Price	Amount
6	Mouse	Intex	5	250	1250
7	Keyboard	Hp	4	400	1600
8	Mouse	Intex	2	260	520
9	Keyboard	Intex	3	420	1260
10	Mouse	Intex	8	280	2240
11	Keyboard	Hp	5	300	1500
12	Mouse	Hp	6	230	1380

 

Dmax Function

 

=DMAX(A5:E12,D5,A1:B2)

 

	A	B	C	D	E
1	Item	Company	Max Price
2	Keyboard	Intex	420
3
4
5	Item	Company	Qty.	Price	Amount
6	Mouse	Intex	5	250	1250
7	Keyboard	Hp	4	400	1600
8	Mouse	Intex	2	260	520
9	Keyboard	Intex	3	420	1260
10	Mouse	Intex	8	280	2240
11	Keyboard	Hp	5	300	1500
12	Mouse	Hp	6	230	1380

 

Dmin Function

 

=DMIN(A5:E12,D5,A1:B2)

 

	A	B	C	D	E
1	Item	Company	Min Price
2	Keyboard	Intex	420
3
4
5	Item	Company	Qty.	Price	Amount
6	Mouse	Intex	5	250	1250
7	Keyboard	Hp	4	400	1600
8	Mouse	Intex	2	260	520
9	Keyboard	Intex	3	420	1260
10	Mouse	Intex	8	280	2240
11	Keyboard	Hp	5	300	1500
12	Mouse	Hp	6	230	1380

 

Dproduct Function

 

=DPRODUCT(A5:E12,D5,A1:B2)

 

	A	B	C	D	E
1	Item	Company	Min Price
2	Keyboard	HP	120000
3
4
5	Item	Company	Qty.	Price	Amount
6	Mouse	Intex	5	250	1250
7	Keyboard	Hp	4	400	1600
8	Mouse	Intex	2	260	520
9	Keyboard	Intex	3	420	1260
10	Mouse	Intex	8	280	2240
11	Keyboard	Hp	5	300	1500
12	Mouse	Hp	6	230	1380