Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count something that has two fields that must be true | Excel Discussion (Misc queries) | |||
Count if 2 conditions are true | Excel Worksheet Functions | |||
returning a count if two conditions are met | Excel Worksheet Functions | |||
count unique with conditions | Excel Worksheet Functions | |||
How do I count number of cels the matches 2 conditions ? | Excel Worksheet Functions |