Excel 2007 Countif? with multiple criteria on multiple text

[jcl82]

Thanks in advance for any help!

A	B
I like to go to texas and montana	texas and florida smell
I am from florida	yay ohio is the best
california has cows	newyork woohoo
montana is awesome	texas is awesome too

<tbody>
</tbody>

C	D
texas	texas
montana	ohio

<tbody>
</tbody>

Using the example above (this is a grossly abridged version), i'm trying to do a modification on the COUNTIF(S) statement. C is criteria for A, and D is criteria for B.

Stepwise I'd like the function to think this way:
If A1 contains any in C OR B1 contains any in D, then count +1
if A2 contains any in C OR B2 contains any in D, then count +1
if A3 contains any in C OR B3 contains any in D, then count +1
if A4 contains any in C OR B4 contains any in D, then count +1

So while the number of CELLS that meet the criteria is 5, the number of lines that meet the criteria is 3. The long way that I've thought of doing this (below) is basically adding up all the cells individually, then subtracting those that are duplicates. However I have a lot more criteria and a lot more cells, so both the formula and the computing time would be exhaustive.

=countif(A:A,"*"&C1&"*")+countif(A:A,"*"&C2&"*")+countif(B:B,"*"&D1&"*")+countif(B:B,"*"&D2&"*")-countifs(A:A,"*"&C1&"*",A:A,"*"&C2&"*")-countifs(A:A,"*"&C1&"*",B:B,"*"&C1&"*")...........-countifs(A:A,"*"&C1&"*",A:A,"*"&C2&"*",B:B,"*"&D1&"*",B:B,"*"&D2&"*")

 

[Ron Coderre]

To avoid sluggishness, I don't know that I'd reference an entire column.
However, using your posted scenario, this regular formula returns: 3

=COUNT(INDEX(1/(ISNUMBER(SEARCH(C1,$A$1:$A$10000))+ISNUMBER(SEARCH(C2,$A$1:$A$10000))+ISNUMBER(SEARCH(D1,$B$1:$B$10000))+ISNUMBER(SEARCH(D2,$B$1:$B$10000))),0))

Is that something you can work with?

 

[jcl82]

This does work quite well. Is there a way to utilize this with a "sum" function, summing the values of a "column E"? to add onto my last stepwise function concept:

Stepwise I'd like the function to think this way:
If A1 contains any in C OR B1 contains any in D, then add E1
if A2 contains any in C OR B2 contains any in D, then add E2
if A3 contains any in C OR B3 contains any in D, then add E3
if A4 contains any in C OR B4 contains any in D, then add E4

This is kind of why I fell back on countif, since sumif works similarly if not the same. Thanks for your help!

 

[Ron Coderre]

Perhaps if you posted the expected results for Col_E.

Meanwhile...maybe these regular formulas:

E1: =SIGN(COUNT(INDEX(SEARCH($C$1:$C$2,A1),0)))*SIGN(COUNT(INDEX(SEARCH($D$1:$D$2,B1),0)))
E2: =SIGN(COUNT(INDEX(SEARCH($C$1:$C$2,A2),0)))*SIGN(COUNT(INDEX(SEARCH($D$1:$D$2,B2),0)))+E1

Copy E2 down as far as you need.

Does that help?

 

[jcl82]

Hi Ron,

Sorry if i wasn't more clear. The results in E are just dollar amounts. So in end results i would like a total dollar amount for the relevant columns. So if it was

E

300

450

540

600

<tbody>
</tbody>

the result will be $1350, as E1+E2+E4 = 300+450+600

 

[Ron Coderre]

That helps quite a bit...
Try this regular formula:

=SUMPRODUCT(ISNUMBER(1/(INDEX(ISNUMBER(SEARCH(C1,$A$1:$A$10000))+ISNUMBER(SEARCH(C2,$A$1:$A$10000))
+ISNUMBER(SEARCH(D1,$B$1:$B$10000))+ISNUMBER(SEARCH(D2,$B$1:$B$10000)),0)))*E1:E10000)

Yes...it's ugly...but, does that help?

 

[Aladin Akyurek]

Hi Ron,

Sorry if i wasn't more clear. The results in E are just dollar amounts. So in end results i would like a total dollar amount for the relevant columns. So if it was

E

300

450

540

600

<tbody>
</tbody>

the result will be $1350, as E1+E2+E4 = 300+450+600

Perhaps slightly less ugly, anyways a bit general...

Control+shift+enter, not just enter:

=SUM(IF((MMULT(ISNUMBER(SEARCH(TRANSPOSE(IF(C1:C4="","#",C1:C4)),A1:A4))+
  ISNUMBER(SEARCH(TRANSPOSE(IF(D1:D4="","#",D1:D4)),B1:B4)),
   ROW(C1:C4)^0)>0)+0,E1:E4))

 

[jcl82]

Hi Guys,

Sorry Ron, your formula doesn't seem to work for me - it returns a #VALUE. I attempted to troubleshoot it, but its hard to see what index actually outputs for the formula.

However Aladin's formula seems to work for my data. Thanks!