Counting multiple criteria in multiple columns

I use the following array formula to count multiple criteria (for a list of criteria in a helper column, only count once):

=COUNTA($B$2:$B$100)SUMPRODUCT(1(MMULT(TRANSPOSE(1(ISTEXT($D$2:$D$10))),1(ISERROR(SEARCH($D$2:$D$10,TRANSPOSE($B2:$B$100)))))=COUNTA($D$2:$D$10)))

The data is in B:B, and the helper column is in D:D.

I want to incorporate a second column, and if there is a criteria match in either column, then count 1. If I make the data B:C, the formula ends up counting twice, when I only want it to count once.

How can I modify this formula, or is another (array) formula better suited to look at two (adjacent) columns, if it sees the criteria in the helper column, to only count once?

TIA for the help!

{=COUNTA(A1:A8)-SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT(F1:F2))),1*(ISERROR(SEARCH(F1:F2,TRANSPOSE(A1:A8)))))=COUNTA(F1:F2)))}

The - before SUMPRODUCT and 1* was what I was missing.

At any rate, I have two adjacent columns (represented here as A1:A8, but the real sheet would be A:B) in which I want to count values only once, which are derived from a helper column (F1:F2).

I've previously used three columns; one counting 1 or 0 for A, one counting 1 or 0 for B, and a final column counting 1 if either previous column is 1.

I was hoping to consolidate the sheet by only using one column (a la the above formula) to capture the two columns from which I want to count criteria and not have it duplicate; i.e., if I use the above formula and change A1:A8 to A1:B8, I'll get cumulative counts from both columns.

I hope that makes sense. If not, I'm happy to provide further input. TIA for your responses!

Tks for your hint. Here's an attached workbook:

Cheers!

In the enclosed workbook, the correct count should be 6. My organization wants to count the percentage of criteria, of which multiple criteria qualifies, that meet a certain condition.

To complicate things, the relevant criteria are maintained in two columns (I can't control this), and any of the criteria in the helper column (there are about 15 criteria in the live sheet) should only count as 1 across rows.

Further, I want to avoid duplicates across the two columns in which any of the criteria may be met.

Finally, there may be any number of values per column, separated by commas. So the values I'm looking for may be anywhere in a cell, again, across the two columns.

It's preferable I include the values of the helper column into the formula (or array) itself, but it's not a show-stopper.

Having the three columns works (yes/no in columns A and B, with column C counting 1 if A or B is 1, it's just very inelegant.

I already have other columns counting other things, so I'm trying to be efficient, as the live sheet is over 30,000 rows.

TIA for the help!

Apologies for the incomplete/confusing answer-- the four-letter values are unique, and there won't be any partial matches (more than two characters in a row in common) in the column with three-letter/four-number entries.

I just want to count some four-letter entries (and not others), and some three-letter/four number entries (and not others), and not duplicate the count if matching criteria is found in both columns. In other words, even if there is a match from the helper column (which contains countable values found from both columns) in both columns, only count 1.

Hmm . . .

The helper column contains just over a dozen values, with perhaps three or four four-letter codes, while the remainder are three-letter/four-number codes.

I can do the COUNTIFS, but the --- {"= . . . cell reference . . . "*", --- portion I'm repeating for every value, right? Whew, very long formula!

I'll give your solution a shot on the live sheet when I return to work tomorrow. I'll let you know. Cheers!

My mistake for over-simplifying the values in the live sheet.

Yes, there are several values in the helper column, some of which apply to column A (four-letter codes), while others column B (letter/number).

It looks like this:

abcd
efgh
igkl
mnop
zxy1200
zxy1300
zxy1400
zxy1500
zxy1600
zxy1700
zxy1800

I've seen MMULT work in this case, but I haven't seen it for two columns. Still, cheers for your help!

Here's a more accurate representation of the live sheet:

I'll get my request right eventually. Tks for working with me!

That looks fantastic. I use a similar array formula to extract one column's worth to count in exactly the same way.

It works fine on the test data (I'm sure you wouldn't have sent it unless it did).

I have never used the N function, so I guess I learned something new today.

Tks for the education and helping me solve my issue. Problem solved! Cheers!