CODEWITHSUNDEEP
excelexcel-formula
Doctor David Anderson
Doctor David Anderson

Reputation: 274

Count IF one of two criteria are met

I am struggling to work out the solution to what should be as simple problem in excel.

I have a column which contains several letter characters (C, D, P, R, S). Each cell can contain any combination of these.

How can I count the number of cells which contain either: S, C, or S & C. Keep in mind, that if it contains both S & C, I still only want to to be counted once.

Upvotes: 0

Views: 207

Answers (2)

user4039065
user4039065

Reputation:

The SIGN function can convert multiple results from an ISNUMBER and FIND¹ to simply being greater than zero. SUMPRODUCT provides the cyclic calculation to add the results from each row.

        count_multiples

The formula in F4 is,

=SUMPRODUCT(SIGN(ISNUMBER(FIND("C", D2:D9))+ISNUMBER(FIND("S", D2:D9))))

¹ The FIND function is case-sensitive. The SEARCH function is not.

Upvotes: 2

Prasanna
Prasanna

Reputation: 1274

Assuming the column containing the data is "A", create following formula in column B:

=OR(NOT(ISERROR(FIND("s",A:A,1))),NOT(ISERROR(FIND("c",A:A,1))))

Then you can get your answer by using following formula (that you can put in any other cell , say in cell C1)

=COUNTIF(B:B,TRUE)

Note: FIND is case sensitive. If you want to do case insensitive search, use SEARCH function.

Upvotes: 1

Related Questions