DCountA Foumula
|
|
|
|
|
|
|
|
This is the Database range.
|
|
Product
|
Wattage
|
Life Hours
|
Brand
|
Unit Cost
|
Box Quantity
|
Boxes In Stock
|
Value Of Stock
|
|
Bulb
|
200
|
3000
|
Horizon
|
£4.50
|
4
|
3
|
£54.00
|
|
Neon
|
100
|
2000
|
Horizon
|
£2.00
|
15
|
2
|
£60.00
|
|
Spot
|
60
|
|
|
|
|
|
£0.00
|
|
Other
|
10
|
8000
|
Sunbeam
|
£0.80
|
25
|
6
|
£120.00
|
|
Bulb
|
80
|
1000
|
Horizon
|
£0.20
|
40
|
3
|
£24.00
|
|
Spot
|
100
|
unknown
|
Horizon
|
£1.25
|
10
|
4
|
£50.00
|
|
Spot
|
200
|
3000
|
Horizon
|
£2.50
|
15
|
1
|
£37.50
|
|
Other
|
25
|
unknown
|
Sunbeam
|
£0.50
|
10
|
3
|
£15.00
|
|
Bulb
|
200
|
3000
|
Sunbeam
|
£5.00
|
3
|
2
|
£30.00
|
|
Neon
|
100
|
2000
|
Sunbeam
|
£1.80
|
20
|
5
|
£180.00
|
|
Bulb
|
100
|
unknown
|
Sunbeam
|
£0.25
|
10
|
5
|
£12.50
|
|
Bulb
|
10
|
800
|
Horizon
|
£0.20
|
25
|
2
|
£10.00
|
|
Bulb
|
60
|
1000
|
Sunbeam
|
£0.15
|
25
|
1
|
£3.75
|
|
Bulb
|
80
|
1000
|
Sunbeam
|
£0.20
|
30
|
2
|
£12.00
|
|
Bulb
|
100
|
2000
|
Horizon
|
£0.80
|
10
|
5
|
£40.00
|
|
Bulb
|
40
|
1000
|
Horizon
|
£0.10
|
20
|
5
|
£10.00
|
|
|
|
|
|
|
|
|
|
|
Count the
number of products of a particular Brand.
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
Brand
|
These
two cells are the Criteria range.
|
|||
|
|
|
Type the brand name :
|
Horizon
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The COUNT value of Horizon is :
|
8
|
=DCOUNTA(B3:I19,E3,E23:E24)
|
|||
|
|
|
|
|
|
|
|
|
|
What Does It Do ?
|
|
|
|
|
|
|
|
|
This function
examines a list of information and counts the non blank cells in a specified
column.
|
|||||||
|
It counts
values and text items, but blank cells are ignored.
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
Syntax
|
|
|
|
|
|
|
|
|
=DCOUNTA(DatabaseRange,FieldName,CriteriaRange)
|
|
|
|||||
|
The DatabaseRange
is the entire list of information you need to examine, including the
|
|||||||
|
field
names at the top of the columns.
|
|
|
|
|
|||
|
The FieldName
is the name, or cell, of the values to Count, such as "Value Of
Stock" or I3.
|
|||||||
|
The CriteriaRange
is made up of two types of information.
|
|
|
|||||
|
The first set of information is the name,
or names, of the Fields(s) to be used as the basis
|
|||||||
|
for selecting the records, such as the
category Brand or Wattage.
|
|
||||||
|
The second set of information is the
actual record, or records, which are to be selected, such
|
|||||||
|
as Horizon as a brand name, or 100 as the
wattage.
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
Formatting
|
|
|
|
|
|
|
|
|
No
special formatting is needed.
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
Examples
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The
count of a product with an unknown Life Hours value.
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
Product
|
Life Hours
|
|
|
|
|
|
|
|
Bulb
|
unknown
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The number of products is :
|
1
|
=DCOUNTA(B3:I19,D3,E50:F51)
|
|||
|
|
|
|
|
|
|
|
|
|
This is
the same calculation but using the name "Life Hours" instead of the
cell address.
|
|||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
1
|
=DCOUNTA(B3:I19,"Life
Hours",E50:F51)
|
|||
|
|
|
|
|
|
|
|
|
|
The
count of the number of particular
product of a specific brand.
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
Product
|
Brand
|
|
|
|
|
|
|
|
Bulb
|
Horizon
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The count is :
|
5
|
=DCOUNTA(B3:I19,"Product",E61:F62)
|
|||
|
|
|
|
|
|
|
|
|
|
The
count of particular products from specific brands.
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
Product
|
Brand
|
|
|
|
|
|
|
|
Spot
|
Horizon
|
|
|
|
|
|
|
|
Neon
|
Sunbeam
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The count is :
|
3
|
=DCOUNTA(B3:I19,"Product",E68:F70)
|
|||