+ Reply to Thread
Results 1 to 5 of 5

Count based on criteria.

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Count based on criteria.

    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!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Count based on criteria.

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

    Pete

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Count based on criteria.

    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)))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Count based on criteria.

    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?

  5. #5
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Count based on criteria.

    try:

    =sumproduct(COUNTIFS(Jan28YTD!$A$2:$A$6609,$C3,Jan28YTD!$B$2:$B$6609,Tables!K$2:K$15))
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1