Count Occurances if two conditions true
 

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.

Ed Gregory Wrote:
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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=465736

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
--
Gary''s Student


"Ed Gregory" wrote:

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,

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

"Ed Gregory" wrote:

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.

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.

Thanks everyone for responses.


"Ashish Mathur" wrote in message
...
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

"Ed Gregory" wrote:

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.