Count occurances based on certain data

  • Thread starter Thread starter Simon
  • Start date Start date
S

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
 
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
 
Back
Top