COUNTIFS and multiple criteria per cell

[jimbosi]

Hello All,

I'm trying to construct a formula to accurately capture the following data:

ABCD / DEFG, HIJK / LMNO, PQRS
1234 / DEFG / PQRS
ABCD / HIJK, DEFG / PQRS
ABCD / STUV / LMNO

So the formula I'm using is:

=SUM(COUNTIFS(A1:A3,ABCD,B1:B3,"*DEFG*",C1:C3{"*LMNO*","*PQRS*"}))

So for A, my first criteria is ABCD. In B, DEFG wherever it is in the cell. Provided those two criteria are met, I want LMNO or PQRS in C, and only to be counted once.

I want my formula to return 2, and it seems like it's counting every instance in C giving me an incorrect number.

For instance, a sheet I'm working on has 480 entries, meaning if every condition was filled, I should get (only) 480. I'm getting 486. I suspect the formula is double-counting. TIA for the help.

Jim

[ben_hensel]

I don't fully understand the data set. I mean, by inspection I guess I get it, but it's just understanding by induction, it's not like there's any rules saying how long they are or whatever.

Anyway the SUM(COUNTIFS()) looks logically correct, but there's a comma missing after the C1:C3 term in your example code.

COUNTIFS with OR has shown up in the past, if you want to move to a SUMPRODUCT design or something.

As it stands, your problem isn't really clear to me. Can you post an example worksheet?

[RobertMika]

=COUNT(IF(A1:A3="ABCD",IF(ISNUMBER(SEARCH("DEFG",B1:B3)),IF((ISNUMBER(SEARCH("LMNO",C1:C3))+(ISNUMBER(SEARCH("PQrs",C1:C3)))),1))))
Confirm COntrol+Shift+enter

[jimbosi]

Thank you for such prompt answers. To clarify, I want the formula to first look in A. If the formula finds the text I'm looking for, then move to B. B contains multiple (identical length) data separated by commas. Don't count 1 yet. If conditions 1 and 2 are met, go to C. C contains multiple (identical length) data separated by commas. If the formula finds -any- of the desired fields, count 1, even if multiple criteria are found in C. I suspect what I had before is making multiple counts.

On a related note, when I'm pressing Ctrl-Shift-Enter, am I pressing those keys at the conclusion of me having typed out the entire formula? What I see happens is that curly brackets are entered at the beginning and end of the formula.

RobertMika, also thanks for your input. The data is on a work computer, so I'll have to wait until I return to work to see if your suggestion works. I'll also have to look up the syntax of the formula you've provided. One last thing-- C may have as many as 10 criteria. I guess that's going to be a lot of closed parens. At any rate, thanks for the help. I'll let you know how it goes ("Solved" or not).

[RobertMika]

If you got multiple instances(10) it would be better to create a helper column (D) and then sum then up.
If you add the curly brackets yourself Excel will indicate error.

[jimbosi]

Tks for the feedback on Ctrl Shift Enter. With the helper column, the live sheet is about 800 records. For A, there are about 12 different terms. In B, there are about 200 potential terms. For C, where I need the OR that you used IF ISNUMBER, there are perhaps 10 potential terms. So for each A, I'm extracting a specific B coupled with A, contingent on whether a C condition is present. Make sense?

I was hoping the COUNTIFS would work because of the relative simplicity. I ran your formula (thanks again!), and it appears to work on a test sheet I created at home. That sheet, however, is a fraction of the size and complexity of the live sheet.

And as one last thing, I'm guessing it won't matter where in C that the sought-after string appears? The string can appear anywhere in the cell.

TIA once again!

[RobertMika]

It does not but if you got let say "PQRS" and your term to search is PQR you will get 1.
Post your workbook to see what can be done.

Please add reputation if you happy with solution.

[jimbosi]

I tried the above array "=COUNT . . . " on my sheet. My issue is that for the third condition/column, there may be as many as 32 different values in a cell, and I may need to count as many as 12 of them (to equal 1 in this case). In other words, I'm checking the first column (A1:A3="ABCD" . . . ") and the second column (which may have several values among about 200, so multiple values may be in this column "(ISNUMBER(SEARCH("DEFG",B1:B3) . . . " So when the first two conditions are met . . .

. . . the problem I'm having is the vast number of values that may be present in C1:C3 in relation to the values I'm searching for "(ISNUMBER(SEARCH("LMNO",C1:C3))+ . . . "

Can I just keep adding "(ISNUMBER(SEARCH(" linked by a + sign to do an OR search on those several C column criteria? It could be easier if I had a lookup table, whereby I point the formula there, and if it makes a match between what's in the cell and what's in the table, count 1? TIA for the help.

[daddylonglegs]

Hello jimbosi,

If you put your 12 search values for column C in Z1:Z12 (you can choose any location but it needs to be a column) then you can use this array formula

=SUM(IF(MMULT((A1:A10="ABCD")*ISNUMBER(SEARCH("DEFG",B1:B10))*ISNUMBER(SEARCH(TRANSPOSE(Z1:Z12),C1:C10)),(Z1:Z12<>"")+0)>0,1))

That won't count any row more than once

[jimbosi]

The formula works in that it reports accurate results-- thanks! Now I've run into another problem (with the formula). I keep getting the error "excel ran out of resources . . ." Dunno what to do about that. I figured having the helper columns would help keep the calculation overhead low. Guess not. Any ideas?

[daddylonglegs]

How many rows are you using in the formula? If you are using whole columns that isn't a good idea, better to restrict to a specific range

[jimbosi]

You're exactly right. I changed the count to account for just top to bottom. The errors went away. I don't know of a quick way to determine the bottom-most row, so ordinarily I've just picked the entire column. I assess this thread as solved, and I heartily thank everyone who responded, and how quickly they did. As I grow in Excel competence, I'll try to pay it forward. Thanks again.