Counting Data Meeting 3 Criteria in 3 Columns

[davids]

I have database dump with info in several columns. Would like to tabulate count that meets specified criteria.

COLOR NUMBER REGION
----- ------ ------
green 5 FL
blue 6 GA
blue 4 FL
blue 1 FL
red 7 NY

What formula can I write to count numer of occurances of blue in FL having number
between 4 and 6 to produce correct result of 2?

 

[Aladin Akyurek]

On 2002-04-07 13:05, davids wrote:
I have database dump with info in several columns. Would like to tabulate count that meets specified criteria.

COLOR NUMBER REGION
----- ------ ------
green 5 FL
blue 6 GA
blue 4 FL
blue 1 FL
red 7 NY

What formula can I write to count numer of occurances of blue in FL having number
between 4 and 6 to produce correct result of 2?

=SUMPRODUCT((colorrange=E1)*(numberrange>=E2)*(numberrange<=E3)*(regionrange=E4))

where E1 houses the color condition, E2 and E3 the (inclusive) between condition, and E4 the region condition.

 

[Aladin Akyurek]

Alternatively, enter in G1:J2

{"color","number","number","region";
"blue",">=4","<=6","FL"}

assuming that the sample data is A1:C6, you could also use:

=DCOUNT(A1:C6,2,G1:J2)

 

[Dave Hawley]