I have two columns (A:B) that I am trying to do a countif statement for, but
can seem to figure it out. In column A I have "A's" and "I''s" and in
column B I have numbers 1 - 198.

Example
A B
a 150
i 120
a 30
i 150

Result
a i
=150 1 1
=120 1
=90
<90 1

TFTH...

Use SUMPRODUCT(). Here is an example for col A being "a" and col B being <90

=SUMPRODUCT(--(A1:A100="a"),--(B1:B100<90))

The other cases are simple adaptations of this.
--
Gary''s Student - gsnu200800


"tom" wrote:

I have two columns (A:B) that I am trying to do a countif statement for, but
can seem to figure it out. In column A I have "A's" and "I''s" and in
column B I have numbers 1 - 198.

Example
A B
a 150
i 120
a 30
i 150

Result
a i
=150 1 1
=120 1
=90
<90 1

TFTH...

Hi Tom

in D1 enter A, in E1 enter I
In C2 enter 150, C3 120, C$ 90, C5 90
In cell D2
=SUMPRODUCT(($A$1:$A$100=D$1)*($B$1:$B$100=$C2)*
($B$1:$B$100<""))
copy to E2
In cell D3
=SUMPRODUCT(($A$1:$A$100=D$1)*($B$1:$B$100=$C3)*
($B$1:$B$100<$C2)*($B$1:$B$100<""))
copy to E3 and copy D3:E3 to D4

In cell D5
=SUMPRODUCT(($A$1:$A$100=D$1)*($B$1:$B$100<$C5)*
($B$1:$B$100<""))
copy to E5

The formulae do not need to be wrapped onto 2 lines, I just did that to
;prevent the newsreader breaking in an awkward place.
--
Regards
Roger Govier

"tom" wrote in message
...
I have two columns (A:B) that I am trying to do a countif statement for,
but
can seem to figure it out. In column A I have "A's" and "I''s" and in
column B I have numbers 1 - 198.

Example
A B
a 150
i 120
a 30
i 150

Result
a i
=150 1 1
=120 1
=90
<90 1

TFTH...