Multiple Criteria in Count formula

[Phil]

Windows 2000 PRO, Office 2000 PRO

I know I've done this before but I can't seem to find an
example that works or the formulas I used last time!

Two columns:

A B
Area Amount
A 100
A 85
B 50
A 100
B 50

All I want is a count of Amounts greater than or equal to
100 for each area and end up with a table like:

Area Count
A 2
B 0

Why is this being so tough? THe KB shows examples of how
to "Sum" with nested IFs, but when I try it with my data I
only get a 0 or 1 answer.

What am I missing here?

Thanx..

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$2:$A$6=E2),--($B$2:$B$6>=F2))

where A2:B6 houses the sample you provided (excluding the labels), E2 an
Area of interest like A, and F2 a value like 100.

 

[Jason Morin]

For A:

=SUMPRODUCT((A1:A10="A")*(B1:B10>=100))

HTH
Jason
Atlanta, GA

 

[Alan]

Try
=SUMPRODUCT(($A$1:$A$5="A")*(B1:B5>=100))
Regards,

 

[Domenic]

Hi Phil,

Using your table, try the following:

D2 enter the letter A
D3 enter the letter B
etc.

E2, copied down:

=SUMPRODUCT(($A$2:$A$10=D2)*($B$2:$B$10>=100))

Hope this helps!