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

Thanks:  0
Likes:  0

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

1. 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. 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):

 B C D E F 1 1 TRUE 1 3 2 2 FALSE 2 0 3 3 FALSE 3 1 4 4 TRUE 4 2 5 1 TRUE 6 2 FALSE 7 3 TRUE 8 4 FALSE 9 1 TRUE 10 2 FALSE 11 3 FALSE 12 4 TRUE

The formula in F1 is =SUMPRODUCT(--(\$B\$1:\$B\$12=E1),--(\$C\$1:\$C\$12=TRUE)) and just copied down to the other cells.

3. 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 -

 B C D E F 1 1 TRUE FALSE TRUE TRUE 2 2 FALSE FALSE TRUE FALSE 3 3 FALSE TRUE FALSE TRUE 4 4 TRUE FALSE FALSE FALSE 5 1 TRUE TRUE FALSE TRUE 6 2 FALSE FALSE TRUE FALSE 7 3 TRUE TRUE FALSE TRUE 8 4 FALSE FALSE TRUE FALSE 9 1 TRUE TRUE TRUE TRUE 10 2 FALSE TRUE TRUE FALSE 11 3 FALSE FALSE TRUE FALSE 12 4 TRUE FALSE FALSE TRUE

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. 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.

User Tag List

Posting Permissions

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