+ Reply to Thread
Results 1 to 2 of 2

Counting number of data that meets 2 criteria (sumproduct and countifs?)

Hybrid View

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2010
    Posts
    27

    Question Counting number of data that meets 2 criteria (sumproduct and countifs?)

    Hello. I'm posting here quite often lately... having difficulty with several functions I've learned from here

    I am trying to make a cell count the number of data that meets 2 criteria. For example:

    Col.A Col.B
    AAA 10
    AAA 9
    CCC 10
    BBB 10
    DDD 9
    AAA 10
    BBB 8
    BBB 9
    CCC 8

    Cell C1 = <Input criteria 1> AAA
    Cell D1 = <Input criteria 2> 10

    Then in E1, for example, I want to show "2", because there are two instances of AAA with "10".

    This is the formula I am currently using, but it's not showing the correct answer...
    =SUMPRODUCT((A2:A10=C1)*(B2:B10=D1)/COUNTIFS(B2:B10,B2:B10&"",A2:A10,A2:A10&""))

    I tried: =SUMPRODUCT((A2:A10=C1)*(B2:B10=D1)/COUNTIFS(B2:B10,B2:B10&D1,A2:A10,A2:A10&C1)) but this gives me #DIV0 error.

    Could someone please help me solve this? I appreciate your help in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Counting number of data that meets 2 criteria (sumproduct and countifs?)

    If all you're doing is counting the rows where those two criteria are met then you can simply use either:

    =SUMPRODUCT((A2:A10=C1)*(B2:B10=D1))

    Or

    =COUNTIF(A2:A10,C1,B2:B10,D1)
    If I've been of help, please hit the star

+ 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