complex CountIf situation

[rtraut]

I want to count the cells in a given column that meet a certain condition if
the cells in the same row of another column also meet that condition.

Here's a more specific example:

I want to count the cells in K:K where K# > 90 and P# = "E", "I", "O", "R",
or "S"

I thought this would do it:
=COUNTIF(J:J,AND(J:J>90,OR(P="e",P="I",P="o",P="r",P="S"))) , but
that is returning a value of 0 (it should result in 19)

Thanks for any help in advance!

 

[Luke M]

=SUMPRODUCT(--(K2:K200>90),--ISNUMBER(SEARCH(P2200,"E I O R S")))

Note that SUMPRODUCT needs to have arrays of equal size, and you can't
callout the entire column unless using XL 2007.

 

[T. Valko]

=SUMPRODUCT(--(K2:K200>90),--ISNUMBER(SEARCH(P2200,"E I O R S")))

NB: That'll count empty cells in P if K meets the criteria.

 

[rtraut]

Thanks Luke, something is still wrong though. I changed the ranges from k200
and p200 to k2500 and p2500, respectively. There are 19 occurences of a
number > 90 in k2:k2500 where the corresponding cell in p22500 has a value
of either E, I, O, R, or S. For some reason the formula that you provided is
coming back with a value of 82. I set a custom filter on column K for
"greater than 90" and counted all of the occurences of E I O R and S in
column P. Only 19. Am I missing something?