# Excel 2007 Countif? with multiple criteria on multiple text

#### jcl82

##### New Member
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&"*")

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
To avoid sluggishness, I don't know that I'd reference an entire column.
However, using your posted scenario, this regular formula returns: 3
Code:
``=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?

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!

Perhaps if you posted the expected results for Col_E.

Meanwhile...maybe these regular formulas:
Code:
``````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?

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

That helps quite a bit...
Try this regular formula:
Code:
``````=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?

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:
Rich (BB code):
``````=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))``````

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!

Replies
3
Views
581
Replies
3
Views
301
Replies
9
Views
395
Replies
2
Views
1K
Replies
1
Views
395

1,207,169
Messages
6,076,910
Members
446,239
Latest member
Home Nest

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back