+ Reply to Thread
Results 1 to 10 of 10

Counting x in Column A if Column B equals to y

  1. #1
    Registered User
    Join Date
    03-11-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Counting x in Column A if Column B equals to y

    Dear all pro's,

    I'm just a new here. Hope anyone can help. I tried to search everywhere in the internet but i could not find the exact answer i wanted to.

    Cherrs

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,963

    Re: Counting x in Column A if Column B equals to y

    =sumproduct((A1:A100="x")*(B1:B100="y"))
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Registered User
    Join Date
    03-11-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Counting x in Column A if Column B equals to y

    doesnt work. Okay, here is my actual problem.

    ColumnA ColumnB
    1 P
    1 L
    2 P
    3 L
    2 L
    1 P

    I wanted the excel to count number "1" in column A if only the ColumnB =P. The answer should be =2
    How is this possible and what formula to use? Please advice. Thanks.

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,963

    Re: Counting x in Column A if Column B equals to y

    =sumproduct((A1:A100=1)*(B1:B100="p"))

  5. #5
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    564

    Re: Counting x in Column A if Column B equals to y

    You can use this function:
    Please Login or Register  to view this content.
    Note : change the red one for value in column A, change the blue for value in column B
    Click (*) if you received helpful response.

    Regards,
    David

  6. #6
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Counting x in Column A if Column B equals to y

    Hi mugies

    May be: =SUMPRODUCT(--(A1:A6=1),--(B1:B6="P")) extend ranges to suit.

    @ SDCh The OP's profile is Excel 2003. the COUNTIFS is not available in that version.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  7. #7
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    564

    Re: Counting x in Column A if Column B equals to y

    @Kevin UK : Yes, you right, sorry not see the excel version that OP's use.

  8. #8
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,963

    Re: Counting x in Column A if Column B equals to y

    You can also use 'countif' or use a pivot table.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting x in Column A if Column B equals to y

    Quote Originally Posted by popipipo View Post
    You can also use 'countif'...
    COUNTIF won't work in this application as there are multiple separate conditions.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  10. #10
    Registered User
    Join Date
    03-11-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Counting x in Column A if Column B equals to y

    Hi all,
    Got it. Thanks popipipo.

    popipipo Re: Counting x in Column A if Column B equals to y

    =sumproduct((A1:A100=1)*(B1:B100="p"))
    This formula works for me. Now im trying if third condition works.

+ 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