+ Reply to Thread
Results 1 to 6 of 6

COUNTIF based on another column cell values

  1. #1
    Registered User
    Join Date
    06-01-2010
    Location
    TLV Israel
    MS-Off Ver
    Excel 2003
    Posts
    7

    COUNTIF based on another column cell values

    Hi,

    I want to count specific values on Column B based on the Column B value AND based on Column A value. For example

    Column A Column B
    1 Type1 Yes
    2 Type1 Yes
    3 Type1 No
    4 Type2 Yes
    5 Type2 Yes
    5 Type2 Yes
    6 Type1 No

    Count Column B values based grouping by Column A:
    Meaning:
    Type1-Yes: 2
    Type1-No: 2
    Type2-Yes: 3
    Type2-No: 0

    tx

    Oren

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: COUNTIF based on another column cell values

    A Pivot Table would be best - with Column A as Row Field, Column B as Column Field and again set as Data Field (to COUNT)

    This would give you a matrix output of the combinations.

    If you want to use formulae then either use SUMPRODUCT (pre XL2007)

    =SUMPRODUCT(($A$1:$A$6="Type1")*($B$1:$B$6="Yes"))
    etc

    or use concatenation of A & B in a new column C and use COUNTIF, ie:

    C1: =$A1&"@"&$B1
    copied down

    Then

    =COUNTIF($C$1:$C$6,"Type1@Yes")

    of course you can replace the constants with references to cells containing values of interest.

  3. #3
    Registered User
    Join Date
    06-01-2010
    Location
    TLV Israel
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: COUNTIF based on another column cell values

    Thanks mate

  4. #4
    Registered User
    Join Date
    06-01-2010
    Location
    TLV Israel
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: COUNTIF based on another column cell values

    One more please
    I used the SumProduct
    The question is how to count the blanks on column B (also grouped by type)

    tx

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: COUNTIF based on another column cell values

    Did you try using "" as the criteria rather than "Yes" ?

  6. #6
    Registered User
    Join Date
    06-01-2010
    Location
    TLV Israel
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: COUNTIF based on another column cell values

    Stupid me ...how could I not think about it before posting..

    Of course it helps...


    tx

+ 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