Count Rows If Column A OR Column B meet criteria-CodePudding

I am stuck trying to count the number of rows that meet a certain criteria. I have a large database in excel. Now on column A and B, I have a lot of text in cell, but I want to count every row if cell A or B meets my criteria.

Let's say column A has some notes from the morning and column B from the afternoon, and every rows represents a day. Now, let's say I want to count how many days I have mentioned "shopping", morning or evening. I would need a function like this:

// COUNTIF_OR(<range_1>,<criteria_1>,<range_2>,<criteria_2>)
COUNTIF_OR(A1:A100;"*shopping*";B1:B100;"*shopping*")

Most solution I could find on the internet counts the rows if A AND B meet the criteria, instead of A OR B.

Please, help me!

CodePudding user response：

Since your two columns are contiguous, you could use:

=SUMPRODUCT(N(MMULT(N(ISNUMBER(SEARCH("shopping",A1:B100))),{1;1})>0))

though I would be tempted by:

=SUM(COUNTIFS(A:A,{"=";"<>";"="}&"*shopping*",B:B,{"<>";"=";"="}&"*shopping*"))

which, notwithstanding its rather obtuse nature and the fact that it is not readily extendible to more than two columns, does nevertheless mean that you can continue to employ COUNTIFS and so benefit from referencing entire columns (A:A, B:B) with virtually no detriment to calculation performance (which cannot be said for the SUMPRODUCT/MMULT set-up).