CODEWITHSUNDEEP
excelexcel-formula
Alejandro Lee
Alejandro Lee

Reputation: 167

Excel - How to count if certain entries exist in a row

I'm looking to see if there are any combinations of two or more of the following keywords in each row: "articles", "instagram", "facebook"

For example:

If row 642 has "articles" and "instagram" return 1,

If row 642 has "instagram" and "facebook" return 1,

If row 642 has "articles" and "facebook" return 1,

etc..

Can anyone help me get started in the right direction?

enter image description here

Thanks

Upvotes: 0

Views: 47

Answers (2)

Jerry
Jerry

Reputation: 71538

You might be able to simply use a count of non-blank cells, for example:

=COUNTA(C642:E642)

On this row, since you are looking for a combination of 2 or more, you should filter on the results with 2 or more.

Alternatively, if you can change the way you are storing all this information, you could put 1 instead where a certain row has the 'feature':

enter image description here

With the same logic as previously, just add up the numbers and if it is at or larger than the minimum you are looking for, you can get them almost instantaneously with simple addition.

Upvotes: 0

Koby Douek
Koby Douek

Reputation: 16675

Try using the following:

=IF(OR(
      AND(ISNUMBER(SEARCH("instagram", B2&C2&D2)),ISNUMBER(SEARCH("facebook", B2&C2&D2))),
      AND(ISNUMBER(SEARCH("articles", B2&C2&D2)),ISNUMBER(SEARCH("facebook", B2&C2&D2))),
      AND(ISNUMBER(SEARCH("articles", B2&C2&D2)),ISNUMBER(SEARCH("instagram", B2&C2&D2)))
    ),1,0)

Where B1, C1 and D1 in the formula above are the cells in which the words exist.

Result:

enter image description here

Upvotes: 1

Related Questions