complex CountIf situation
 

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:J90,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!

complex CountIf situation
 

=SUMPRODUCT(--(K2:K20090),--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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"rtraut" wrote:

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:J90,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!

complex CountIf situation
 

=SUMPRODUCT(--(K2:K20090),--ISNUMBER(SEARCH(P2:P200,"E I O R S")))

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

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
=SUMPRODUCT(--(K2:K20090),--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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"rtraut" wrote:

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:J90,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!

complex CountIf situation
 

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?

"Luke M" wrote:

=SUMPRODUCT(--(K2:K20090),--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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"rtraut" wrote:

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:J90,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!