multiple criteria count

[HRPRGMNGT]

I have 2 columns in excel, the first one has a business name and the second one has comments. I now want to count how many times a specific word appears in the comments for a specific business. Is there a formula that does that?

<TABLE style="WIDTH: 360pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=479 border=0 x:str><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 263pt; mso-width-source: userset; mso-width-alt: 12800" width=350><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 97pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=129 height=17>Column A</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 263pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=350 x:str="Column B ">Column B
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Business One</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">I think that service is great and really fast</TD></TR>
<TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Business Two</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Products are good service is bad
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Business One</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">I think that service is great</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Business Two</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">I think that service is amazing</TD></TR></TBODY></TABLE>

So for example I need a formula that will let me count that Business One has 2 comments with 'great' in it.

 

[njimack]

Excel Workbook
	A	B	C	D	E
1	Business	Comment		Business to search for:	Business One
2	Business One	I think that service is great and really fast		Comment to search for:	great
3	Business Two	Products are good service is bad
4	Business One	I think that service is great		COUNT	2
5	Business Two	I think that service is amazing
Sheet1

 

[doublej_jj]

Column C is whether "Great" appears or not in the comment, if it does, then a 1 is placed, if not, a zero.

=IF(ISERROR(FIND("Great",B1,1)),0,1) {Copy this down}

You can then sumif or sumproduct

=SUMIF(A2:A8,"Business One",C2:C8) or
=SUMPRODUCT((A2:A8="Business One)*(C2:C8)

Lots of things to do to make this more elegant like refer to a cell instead of typing the criteria of "Business One, but this will hopefully put you on the right path.

Jeff

 

[dfenton21]

Hi There,

This will also work.

=SUMPRODUCT((A1:A4="Business One")*(IF(ISERROR(FIND("great", B1:B4, 1)), 0, 1)=1))

It is an array formula, so you'll need to enter it by pressing CTRL + SHIFT + ENTER

 

[HRPRGMNGT]

Thank you very much everyone, all of the suggestions work great

 

[dfenton21]

You're welcome

 

[AlphaFrog]

If you have Excel 2007 or later, you could use the COUNTIFS function

=COUNTIF(A:A, E1, B:B, "*"&E2&"*")

E1 = "Business One"
E2 = "great"