Countif for two text criteria

E

epyzdrh

New Member
Joined
Jul 30, 2008
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I'm looking for a way to count data in two columns that meet two criteria. The first column in range B19:B143 will contain either Line 1, Line 2 Line 3 or Line 4. The second range (I19:I143) contains a full written sentence of which I need to check for a keyword or part of a keyword and if it matches both criteria count it.

So say the keyword was "stepper" and I want a count of "Line 1" and "stepper", how would I go about this?

Thanks,

David
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
epyzdrh said:
Hi there,

I'm looking for a way to count data in two columns that meet two criteria. The first column in range B19:B143 will contain either Line 1, Line 2 Line 3 or Line 4. The second range (I19:I143) contains a full written sentence of which I need to check for a keyword or part of a keyword and if it matches both criteria count it.

So say the keyword was "stepper" and I want a count of "Line 1" and "stepper", how would I go about this?

Thanks,

David
Click to expand...
Try one of these...

Use cells to hold the criteria:
  • A1 = Line 1
  • B1 = stepper
This one will work in any version of Excel:

=SUMPRODUCT(--(B19:B143=A1),--(ISNUMBER(SEARCH(B1,I19:I143))))

This one will work in Excel 2007 and later:

=COUNTIFS(B19:B143,A1,I19:I143,"*"&B1&"*")
 
Upvote 0
Hi Biff,

This seems to work for Line 1 but just returns zero values for Lines 1-3. This is the formula;

=SUMPRODUCT(--(C19:C143=J5),--(ISNUMBER(SEARCH($E$6,$I$19:$I$143))))

where;

E6 = Rotary
J5 = Line 2
 
Upvote 0
Hi

I used a helper column with the formula.
I then just did a normal countif on the helper column.

Alternatively, a non-formula based solution is to create a pivot table with the 2 columns as row lables with the count in the values box and then filter column 2 for any lines which contain the word "stepper".

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 99px"><COL style="WIDTH: 123px"><COL style="WIDTH: 233px"><COL style="WIDTH: 94px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Helper column</TD><TD>Line </TD><TD>Column 2</TD><TD>Line 1</TD><TD>stepper</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD>Line 1</TD><TD>In this sentence is the word stepper</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 2</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 3</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 1</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 2</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 3</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 1</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 2</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 3</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD>Line 1</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 2</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 3</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD>Line 1</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 2</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 3</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 1</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 2</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 3</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 1</TD><TD>In this sentence is the word </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 2</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD>Line 3</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD>Line 1</TD><TD>In this sentence is the word stepper</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD> </TD><TD>Count of Column 2</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD> </TD><TD>Column 1</TD><TD>Column 2</TD><TD>Total</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD> </TD><TD>Line 1</TD><TD>In this sentence is the word stepper</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD> </TD><TD>Line 2</TD><TD>In this sentence is the word stepper</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD> </TD><TD>Line 3</TD><TD>In this sentence is the word stepper</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD> </TD><TD>Grand Total</TD><TD> </TD><TD style="TEXT-ALIGN: right">11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A3</TD><TD>=AND(B3=$D$2, ISNUMBER(FIND($E$2,C3))=TRUE)</TD></TR><TR><TD>D3</TD><TD>= COUNTIF($A$3:$A$24,TRUE)</TD></TR><TR><TD>A4</TD><TD>=AND(B4=$D$2, ISNUMBER(FIND($E$2,C4))=TRUE)</TD></TR><TR><TD>A5</TD><TD>=AND(B5=$D$2, ISNUMBER(FIND($E$2,C5))=TRUE)</TD></TR><TR><TD>A6</TD><TD>=AND(B6=$D$2, ISNUMBER(FIND($E$2,C6))=TRUE)</TD></TR><TR><TD>A7</TD><TD>=AND(B7=$D$2, ISNUMBER(FIND($E$2,C7))=TRUE)</TD></TR><TR><TD>A8</TD><TD>=AND(B8=$D$2, ISNUMBER(FIND($E$2,C8))=TRUE)</TD></TR><TR><TD>A9</TD><TD>=AND(B9=$D$2, ISNUMBER(FIND($E$2,C9))=TRUE)</TD></TR><TR><TD>A10</TD><TD>=AND(B10=$D$2, ISNUMBER(FIND($E$2,C10))=TRUE)</TD></TR><TR><TD>A11</TD><TD>=AND(B11=$D$2, ISNUMBER(FIND($E$2,C11))=TRUE)</TD></TR><TR><TD>A12</TD><TD>=AND(B12=$D$2, ISNUMBER(FIND($E$2,C12))=TRUE)</TD></TR><TR><TD>A13</TD><TD>=AND(B13=$D$2, ISNUMBER(FIND($E$2,C13))=TRUE)</TD></TR><TR><TD>A14</TD><TD>=AND(B14=$D$2, ISNUMBER(FIND($E$2,C14))=TRUE)</TD></TR><TR><TD>A15</TD><TD>=AND(B15=$D$2, ISNUMBER(FIND($E$2,C15))=TRUE)</TD></TR><TR><TD>A16</TD><TD>=AND(B16=$D$2, ISNUMBER(FIND($E$2,C16))=TRUE)</TD></TR><TR><TD>A17</TD><TD>=AND(B17=$D$2, ISNUMBER(FIND($E$2,C17))=TRUE)</TD></TR><TR><TD>A18</TD><TD>=AND(B18=$D$2, ISNUMBER(FIND($E$2,C18))=TRUE)</TD></TR><TR><TD>A19</TD><TD>=AND(B19=$D$2, ISNUMBER(FIND($E$2,C19))=TRUE)</TD></TR><TR><TD>A20</TD><TD>=AND(B20=$D$2, ISNUMBER(FIND($E$2,C20))=TRUE)</TD></TR><TR><TD>A21</TD><TD>=AND(B21=$D$2, ISNUMBER(FIND($E$2,C21))=TRUE)</TD></TR><TR><TD>A22</TD><TD>=AND(B22=$D$2, ISNUMBER(FIND($E$2,C22))=TRUE)</TD></TR><TR><TD>A23</TD><TD>=AND(B23=$D$2, ISNUMBER(FIND($E$2,C23))=TRUE)</TD></TR><TR><TD>A24</TD><TD>=AND(B24=$D$2, ISNUMBER(FIND($E$2,C24))=TRUE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


But T Valko's formula is definitely more simple and seems to work for me.
 
Upvote 0
epyzdrh said:
Hi Biff,

This seems to work for Line 1 but just returns zero values for Lines 1-3. This is the formula;

=SUMPRODUCT(--(C19:C143=J5),--(ISNUMBER(SEARCH($E$6,$I$19:$I$143))))

where;

E6 = Rotary
J5 = Line 2
Click to expand...
Well, that sounds like you might have a data problem. The data you see might not be the data that you actually have. You might have unseen whitespace characters like this:

[space]Line 2
Line 2[space]
[space]Line 2[space]

Do you get the correct count of Line 2 with this formula:

=SUMPRODUCT(--(TRIM(C19:C143)="Line 2"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Macro gives message box every time something is deleted
Replies
7
Views
236
Peter_SSs
Peter_SSs
D
  • Solved
SUM of Columns based on multiple criteria with User Form
2
Replies
15
Views
332
Alex Blakenburg
A
P
  • Question Question
Countif using totals of two named columns
2 3
Replies
20
Views
579
Cubist
Cubist
S
  • Question Question
Multiple Criteria Lookup Return Value
Replies
1
Views
260
Vogateer
V
A
  • Solved
COUNT multiple SUM COUNTIFS
Replies
6
Views
340
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top