Count Occurances if two conditions true

[Ed Gregory]

I have a range a1:a150 that can contain"rx". I have a second range b1:b150
that can contain "oem"

I want to count the occurrences when a1:a150 contains "rx" AND b1:b150
contains "oem". The key is AND.

Right now I use countif to return number of number occurrences for each
range, but I need a third count and that is when both are true return a
count.

Thanks.

 

[Paul Sheppard]

I have a range a1:a150 that can contain"rx". I have a second range
b1:b150
that can contain "oem"

I want to count the occurrences when a1:a150 contains "rx" AND b1:b150
contains "oem". The key is AND.

Right now I use countif to return number of number occurrences for
each
range, but I need a third count and that is when both are true return
a
count.

Thanks.

Hi Ed

Try > =SUM((A1:A15o="rx")*(B1:B150="oem")), enterred as an array, type
the formula in and then CTRL/SHIFT/ENTER

 

[Guest]

Ed, you are in luck! you don't need any COUNTIF()'s or any other functions.

Use a helper column, say column Z, and enter in Z1:

=(A1="rx")*(B1="oem")
and copy down the column.
Finally, addup the ones in column Z

( the expressions in parens are boolean and the multiply represents the AND

 

[Guest]

Hi,

You can use an array formula (Ctrl+Shift+Enter) to solve your problem.

=sum(if((a1:a150="rx")*(b1:b150="oem"),1,0))

Regards,

Ashish Mathur

 

[0-0 Wai Wai ^-^]

Try > =SUM((A1:A15o="rx")*(B1:B150="oem")), enterred as an array, type
the formula in and then CTRL/SHIFT/ENTER

There's a typo (A15o) in the formula.
Use this instead:
=SUM((A1:A150="rx")*(B1:B150="oem"))
Remember: Finish it by pressing Ctrl+Shift+Enter all together

As a reminder, we need to type Ctrl+Shift+Enter every time you change/edit the
formula or you double click the cell, or you may mistekenly change it back as a
simple function (NOT an array).
Also we can't set it as an array simply by enclosing the formula with { }, ie
{=SUM((A1:A15o="rx")*(B1:B150="oem")) }. We CANNOT do it in that way.

 

[Ed Gregory]

Thanks everyone for responses.

Hi,

You can use an array formula (Ctrl+Shift+Enter) to solve your problem.

=sum(if((a1:a150="rx")*(b1:b150="oem"),1,0))

Regards,

Ashish Mathur