Results 1 to 4 of 4

Need to check column B, THEN if true, count column C

This is a discussion on Need to check column B, THEN if true, count column C within the Excel Questions forums, part of the Question Forums category; This is what I tried: =IF($B:$B=1,(COUNTIF(C2:C33,TRUE)),(0)) Column B has the numbers 1-4. I have to do the same thing for ...

  1. #1
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default Need to check column B, THEN if true, count column C

    This is what I tried:
    =IF($B:$B=1,(COUNTIF(C2:C33,TRUE)),(0))
    Column B has the numbers 1-4. I have to do the same thing for each number, and get a count of how many times column C is true with the number. The formula I wrote there works for when column B is 1, but I can't get it to work for when the value is 2. I would also ideally like to not have to include a specific set of numbers, other than maybe just the total number of rows (110).

    Thanks for the help if you can offer it.

  2. #2
    MrExcel MVP, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    Default Re: Need to check column B, THEN if true, count column C

    The easiest thing would be to simply list the numbers in a column, then have the counting formula in the next column referencing that.
    So instead of B:B=1, you would have B:B=D1 or whatever cell has the "1" in it.
    For example, I did that here, except I used a SUMPRODUCT formula to get the results (note with SUMPRODUCT you have to use a specific range--you can't refer to entire columns such as B:B):

    BCDEF
    11TRUE13
    22FALSE20
    33FALSE31
    44TRUE42
    51TRUE
    62FALSE
    73TRUE
    84FALSE
    91TRUE
    102FALSE
    113FALSE
    124TRUE


    The formula in F1 is =SUMPRODUCT(--($B$1:$B$12=E1),--($C$1:$C$12=TRUE)) and just copied down to the other cells.
    Last edited by Von Pookie; Jul 9th, 2010 at 04:49 PM.
    Kristy

  3. #3
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default Re: Need to check column B, THEN if true, count column C

    Thank you so much, that worked perfectly!

    Now, I have a question to try and take this one step further -

    BCDEF
    11TRUEFALSETRUETRUE
    22FALSEFALSETRUEFALSE
    33FALSETRUEFALSETRUE
    44TRUEFALSEFALSEFALSE
    51TRUETRUEFALSETRUE
    62FALSEFALSETRUEFALSE
    73TRUETRUEFALSETRUE
    84FALSEFALSETRUEFALSE
    91TRUETRUETRUETRUE
    102FALSETRUETRUEFALSE
    113FALSEFALSETRUEFALSE
    124TRUEFALSEFALSETRUE


    The rows hilighted in red where only column E is true, i would like to count how many of these ONLY there are in each group.
    Forgive me, I haven't touched excel in about a year, and i'm getting rather frustrated with my lack of memory.

  4. #4
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default Re: Need to check column B, THEN if true, count column C

    Actually, I solved my own problem on that one. For my larger table, I used:
    =SUMPRODUCT(--($B$2:$B$111=$L9),--($H$2:$H$111=TRUE),--($C$2:$C$111=FALSE),--($D$2:$D$111=FALSE),--($E$2:$E$111=FALSE),--($F$2:$F$111=FALSE),--($I$2:$I$111=FALSE),--($J$2:$J$111=FALSE))

    seems to work just fine.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com