Count occurances based on certain data

[Simon]

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

 

[Andy]

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(A17,"Colour",A1112)

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!