Count based on criteria.

**The Phil** · 01-29-2013, 03:43 PM

I have a 2 columns and want to count the number of times there is a particular match. So I want to look in the table called "Table". In cell B2 (which is not in the table) I have a word. In cells C2:C10, also not in "Table" I have several words. What I want to do is search for every row in "Table" that contains the B2 value in column 1 and any of the values from C2:C10 in column 2.

So in B2 I have "Word" and in column C I have several words like "it", "as", "in", etc

The table would look like:

Word it
Word not
Doc as
Seven in

My formula should return a result of 1.

I've been experimenting with dcount, countif, etc, but I'm just not getting it.

Thanks!

**Pete_UK** · 01-29-2013, 03:48 PM

As you have 2 criteria you need to use COUNTIFS or SUMPRODUCT. What is the range (cell references) that your Table covers?

Pete

**Ace_XL** · 01-29-2013, 03:53 PM

If your table range is E2:F20

try this

=SUMPRODUCT((E2:E20=$B$2)*((F2:F20=C2)+(F2:F20=C3)+(F2:F20=C4)+(F2:F20=C5)+(F2:F20=C6)+(F2:F20=C7)+(F2:F20=C8)+(F2:F20=C9)+(F2:F20=C10)))

**The Phil** · 01-29-2013, 05:15 PM

Sorry Ace, I need it to point to a range of possible words so that I can easily update it.

I tried the following: =COUNTIFS(Jan28YTD!$A$2:$A$6609,"="&$C3,Jan28YTD!$B$2:$B$6609,Tables!K$2:K$15)

This type of formula works with SUMIFS but in this case it is only producing results for the first value in the Tables!K$2:K$15 range. Any suggestions?