Count occurances based on certain data
 

Given columns of data, I need to count occurances, based
on certain data.

aa 0123 red 3
ab 0125 red 3
ac 0452 blue 5
ba 0478 white 12
bb 0845 white 1
bc 0654 red 54

I need to only look at data that starts with a "b" (first
column), then I want to count how many colours (3rd
column) have numbers less than "4" (last column).

I suppose that the function should have the colour as an
argument, so when it is called it will return the number
of occurances.

Can anyone help me please.

Simon

Count occurances based on certain data
 

Simon

Try DCOUNTA

You need column headers for your data. If it starts in cell A1 and
looks like this:

A B C D
1 Ref No. Colour Figure
2 aa 0123* *red ** 3
3 ab 0125* *red ** 3
4 ac 0452* *blue * 5
5 ba 0478* *white 12
6 bb 0845* *white 1
7 bc 0654* *red ** 54

Then you need to add criteria (I have assumed this is added in cells A11
to D12 as follows:

A B C D
11 Ref No. Colour Figure
12 b* <4

I have included No. and Colour in case you want to get more specific
with your criteria.

The formula is then:

=DCOUNTA(A1:D7,"Colour",A11:D12)

and this gives me the answer "1".

Hope that helps

Andy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!