ExcelBanter - Count if with Multiple criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Count if with Multiple criteria (https://www.excelbanter.com/excel-worksheet-functions/268859-count-if-multiple-criteria.html)

Count if with Multiple criteria

I have a column which can contain text as below :

Competency
Capability
Capability & Competency

I want to count all the cells which contains either competency / capability or both .

The count of the above should be 3 and not 4 .

If we do 2 separate count if and add it will give 4 but what i need it to count is only 3 . ( i.e no of unique rows which has any one of this input ) .

Can anyone help me how to do this .

Dear SVS, Good Afternoon.

The result 4 is CORRECT!

But if you can received a different result you must change the logic process.

Try to search for
"Competency"

"Capability"

"Capability & Competency" (the text must be equal at cells)

Tell me if it worked for you.
-----------------------------------------------------------------------

Quote:

Originally Posted by SVS (Post 962995)

i cannot understand the reply .

Yes i agree the count will be 4 .

I am using the formula as below :

countif(a1:a3,"capability")+countif(a1:a3,"compete ncy")

the above formula gives the result as 4 as it does the count separately for capability and separately for competency .

But i have only three rows .

As the no of rows is only 3, i want it to count 3 and not 4

Is there any way we can merge these formula so that it checks the rows only once and if any 1 condition satisifies , the row will be counted ..Otherwise it will not be counted .