complex CountIf situation

  • Thread starter Thread starter rtraut
  • Start date Start date
R

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:P="e",P:P="I",P:P="o",P:P="r",P:P="S"))) , but
that is returning a value of 0 (it should result in 19)

Thanks for any help in advance!
 
=SUMPRODUCT(--(K2:K200>90),--ISNUMBER(SEARCH(P2:P200,"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.
 
=SUMPRODUCT(--(K2:K200>90),--ISNUMBER(SEARCH(P2:P200,"E I O R S")))

NB: That'll count empty cells in P if K meets the criteria.
 
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 p2:p2500 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?
 
Back
Top